package com.classes;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.TreeMap;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;

import com.mbbmap.app.manager.ConfigManager;
import com.mbbmap.app.manager.DBConnectionManager;
import com.mbbmap.util.Constants;

public class Report {
	private static String message;
	public static final String reportAction []= {
		"Credit Check Result Attached",
		"Request Credit Check",
		"Incomplete Document",
		"Reject - Data Discrepancy",
		"Reject - Document Discrepancy",
		"Reject - Data and Document Discrepancy",
		"Credit Check Incomplete",
		"Request RMS Review (FRAUD)",
		"Recommended by RMS",
		"Not Recommended by RMS",
		"RMS Not Recommended, Request Decision",
		"Pre MID Setup Validation Done",
		"Data Discrepancy",
		"Doc Discrepancy",
		"Data & Doc Discrepancy",
		"Pending Original Doc",
		"Request Final Approval for MID Setup",
		"Pre MID Setup, Validation Done",
		"MID Setup Done. Request Verification",
		"Incomplete Doc. But Proceed for MID Setup",
		"Doc/ Info Provided. Request For MID Setup",
		"Incomplete. To Provide Doc/Info",
		"Amendment Required",
		"Amendment Done",
		"Verified. Filing Complete",
		"Verified. Pending Document",
		"Request For MID Setup",
		"Additional Document Received"	
	};
	public static String getMessage() {
		return "[Report] " + Report.message; 
	}
	
	static final long ONE_HOUR = 60 * 60 * 1000L;
	private static long daysBetween(Date start, Date end) {
		return ( (end.getTime() - start.getTime() + ONE_HOUR) /
                (ONE_HOUR * 24));
	}
	
	private static long daysBetween(Date start, Date end, ArrayList<Date> listHoliday) {
		System.out.println("Start: " + start.toString() + ", End: " + end.toString());
		 Calendar startCal = Calendar.getInstance();
		 startCal.setTime(start);        

		 Calendar endCal = Calendar.getInstance();
		 endCal.setTime(end);

		 int workDays = 0;

		 //Return 0 if start and end are the same
		 if (startCal.getTimeInMillis() == endCal.getTimeInMillis()) {
			 return 0;
		 }

		 if (startCal.getTimeInMillis() > endCal.getTimeInMillis()) {
			 startCal.setTime(end);
		     endCal.setTime(start);
		 }

		 do {
			 //excluding start date
		     startCal.add(Calendar.DAY_OF_MONTH, 1);
		     if (startCal.get(Calendar.DAY_OF_WEEK) != Calendar.SATURDAY 
		    		 && startCal.get(Calendar.DAY_OF_WEEK) != Calendar.SUNDAY ) {
		    	 if(listHoliday != null && listHoliday.size() > 0 && !listHoliday.contains(startCal.getTime())){
		    		 ++workDays;
		    	 }
		     }
		 } while (startCal.getTimeInMillis() < endCal.getTimeInMillis()); //excluding end date

		 return workDays;
	}
	
	public static ArrayList<TreeMap<String, String>> getTerminalInstallation(TreeMap<String, String> search) {
		ArrayList<TreeMap<String, String>> rArr = new ArrayList<TreeMap<String, String>>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "select rptterminalinstall.*, c_reference_no, name "
						+ "from rptterminalinstall "
						+ "left join casemast on rpt_case_id = c_id "
						+ "left join tbluser on rpt_creator = login_id "
						+ "where rpt_date >= to_date(?, 'DD/MM/YYYY') "
						+ "	and rpt_date <= to_date(?, 'DD/MM/YYYY')";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setString(1, search.get("from_date"));
					statement.setString(2, search.get("to_date"));
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							TreeMap<String, String> rRow = new TreeMap<String, String>();
							rRow.put("reference_no", resultSet.getString("c_reference_no"));
							rRow.put("vendor", resultSet.getString("rpt_vendor"));
							rRow.put("serial_no", resultSet.getString("rpt_serial_no"));
							rRow.put("area", resultSet.getString("rpt_area"));
							rRow.put("job_type", resultSet.getString("rpt_job_type"));
							rRow.put("stock", resultSet.getString("rpt_stock"));
							rRow.put("creator", resultSet.getString("rpt_creator"));
							rRow.put("sent_date", resultSet.getString("rpt_sent_date"));
							rRow.put("completed_date", resultSet.getString("rpt_completed_date"));
							rRow.put("sla", resultSet.getString("rpt_sla"));
							rRow.put("reschedule_date", resultSet.getString("rpt_reschedule_date"));
							rRow.put("status", resultSet.getString("rpt_status"));
							rRow.put("wireless_stock", resultSet.getString("rpt_wireless_stock"));
							rRow.put("debit", resultSet.getString("rpt_debit"));
							rRow.put("merchant_name", resultSet.getString("rpt_merchant_name"));
							rRow.put("address", resultSet.getString("rpt_address"));
							rRow.put("amex_pos", resultSet.getString("rpt_amex_pos"));
							rRow.put("amex_moto", resultSet.getString("rpt_amex_moto"));
							rRow.put("partners", resultSet.getString("rpt_partners"));
							rRow.put("pos_mid", resultSet.getString("rpt_pos_mid"));
							rRow.put("pos_tid", resultSet.getString("rpt_pos_tid"));
							rRow.put("ezpay", resultSet.getString("rpt_ezpay"));
							rRow.put("ezpay_amex", resultSet.getString("rpt_ezpay_amex"));
							rRow.put("acceptance", resultSet.getString("rpt_acceptance"));
							rRow.put("others", resultSet.getString("rpt_others"));
							rRow.put("hotel_function", resultSet.getString("rpt_hotel_function"));
							rRow.put("remark", resultSet.getString("rpt_remark"));
							
							if(resultSet.getString("name") != null){
								rRow.put("creator", resultSet.getString("name"));
							}
							
							if(resultSet.getTimestamp("rpt_sent_date") != null){
								long t = ((Timestamp) resultSet.getTimestamp("rpt_sent_date")).getTime();
								rRow.put("sent_date", new SimpleDateFormat("dd/MM/yyyy HH:mm").format(new Date(t)));
							}
							
							if(resultSet.getTimestamp("rpt_completed_date") != null){
								long t = ((Timestamp) resultSet.getTimestamp("rpt_completed_date")).getTime();
								rRow.put("completed_date", new SimpleDateFormat("dd/MM/yyyy HH:mm").format(new Date(t)));
							}
							
							if(resultSet.getTimestamp("rpt_reschedule_date") != null){
								long t = ((Timestamp) resultSet.getTimestamp("rpt_reschedule_date")).getTime();
								rRow.put("reschedule_date", new SimpleDateFormat("dd/MM/yyyy HH:mm").format(new Date(t)));
							}
							
							rArr.add(rRow);
						}
					
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
				
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return rArr;
		
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static ArrayList<TreeMap<String, String>> getTRF(TreeMap<String, String> search) {
		ArrayList<TreeMap<String, String>> rArr = new ArrayList<TreeMap<String, String>>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "select rpttrf.*, merchant.*, c_reference_no "
						+ "from rpttrf "
						+ "left join casemast on rpt_case_id = c_id "
						+ "left join outlet on rpttrf.rpt_outlet_id = outlet.o_id "
						+ "left join merchant on outlet.o_merchant_id = merchant.m_id "
						+ "where rpt_date >= to_date(?, 'DD/MM/YYYY') "
						+ "	and rpt_date <= to_date(?, 'DD/MM/YYYY')";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setString(1, search.get("from_date"));
					statement.setString(2, search.get("to_date"));
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							String rental = String.format("%10.2f", resultSet.getDouble("rpt_rental"));
							String amount = String.format("%10.2f", resultSet.getDouble("rpt_amount"));
							String adv_amount = String.format("%10.2f", resultSet.getDouble("rpt_adv_amount"));
							String first_rental = String.format("%10.2f", resultSet.getDouble("rpt_1st_rental"));
							
							TreeMap<String, String> rRow = new TreeMap<String, String>();
							rRow.put("map_ref_no", resultSet.getString("c_reference_no"));
							rRow.put("oic", resultSet.getString("rpt_creator"));
							rRow.put("merchant_name", resultSet.getString("m_name"));
							rRow.put("mid", resultSet.getString("rpt_mid"));
							rRow.put("tid", resultSet.getString("rpt_tid"));
							rRow.put("wired_wireless", resultSet.getString("rpt_terminal_type"));
							rRow.put("vendor", resultSet.getString("rpt_vendor"));
							rRow.put("account", resultSet.getString("rpt_account"));
							rRow.put("bus_reg", resultSet.getString("rpt_bus_reg"));
							rRow.put("rental", rental);
							rRow.put("install_date", resultSet.getString("rpt_install_date"));
							rRow.put("cheque_no", resultSet.getString("rpt_cheque_no"));
							rRow.put("cheque_date", resultSet.getString("rpt_cheque_date"));
							rRow.put("bank_branch", resultSet.getString("rpt_bank_branch"));
							rRow.put("amount", amount);
							rRow.put("adv_amount", adv_amount);
							rRow.put("first_rental", first_rental);
							
							rArr.add(rRow);
						}
					
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
				
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
		}catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
		return rArr;
	}
	
	public static ArrayList<TreeMap<String, String>> getSlaProcess(TreeMap<String, String> search) {
		ArrayList<TreeMap<String, String>> rArr = new ArrayList<TreeMap<String, String>>();
	
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
				
				if(search != null) {
					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						q_search += "and c_group_code = ? ";
					}
					
					if(search.containsKey("group_user") && search.get("group_user").length() > 0){
						q_search += "and c_created_by = ? ";
					}
					
					if(search.containsKey("application_type") && search.get("application_type").length() > 0){
						q_search += "and c_type = ? ";
					}
					
					if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
						q_search += "and c_reseller_location = ? ";
					}
				}
				
				
	
				String sql = "select tbl.rpt_case_id, c_reference_no, c_type, tbl.rpt_matrix, tblaccessrole.role_name, tbluser.name, c_reseller_location, "
						+ "		a.rpt_status as status_1, b.rpt_status as status_2, c.rpt_status as status_3, d.rpt_status as status_4, e.rpt_status as status_4a, f.rpt_status as status_5, g.rpt_status as status_5a, h.rpt_status as status_6, i.rpt_status as status_7, "
						+ "		process_1, process_2, process_3, process_4, process_4a, process_5, process_5a, process_6, process_7, total_sla as current_sla, null as end_to_end_sla, "
						+ "		(case when total_sla is not null and process_4a is not null then total_sla - process_4a else null end) as current_4a, "
						+ "		(case when total_sla is not null and process_5a is not null then total_sla - process_5a else null end) as current_5a "
						+ "from ( "
						+ "		select rpt_case_id, rpt_matrix, rpt_date, "
						+ "		sum(case when rpt_process = '1' then rpt_sla else null end) as process_1, "
						+ "		sum(case when rpt_process = '2' then rpt_sla else null end) as process_2, "
						+ "		sum(case when rpt_process = '3' then rpt_sla else null end) as process_3, "
						+ "		sum(case when rpt_process = '4' then rpt_sla else null end) as process_4, "
						+ "		sum(case when rpt_process = '4a' then rpt_sla else null end) as process_4a, "
						+ "		sum(case when rpt_process = '5' then rpt_sla else null end) as process_5, "
						+ "		sum(case when rpt_process = '5a' then rpt_sla else null end) as process_5a, "
						+ "		sum(case when rpt_process = '6' then rpt_sla else null end) as process_6, "
						+ "		sum(case when rpt_process = '7' then rpt_sla else null end) as process_7, "
						+ "		sum(rpt_sla) as total_sla "
						+ "		from rptslaprocess "
						+ "		join casemast on rpt_case_id = c_id "
						+ "		where c_status not like 'CLOSE%' "
						+ "		and rpt_date = to_date(?, 'DD/MM/YYYY') "
						+ q_search
						+ "		group by rpt_case_id, rpt_matrix, rpt_date "
						+ "		order by rpt_case_id asc "
						+ ") tbl "
						+ "join casemast on rpt_case_id = c_id "
						+ "join tblaccessrole on role_code = c_group_code "
						+ "join tbluser on login_id = c_created_by "
						+ "left join rptslaprocess a on tbl.rpt_case_id = a.rpt_case_id and a.rpt_process = '1' and a.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess b on tbl.rpt_case_id = b.rpt_case_id and b.rpt_process = '2' and b.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess c on tbl.rpt_case_id = c.rpt_case_id and c.rpt_process = '3' and c.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess d on tbl.rpt_case_id = d.rpt_case_id and d.rpt_process = '4' and d.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess e on tbl.rpt_case_id = e.rpt_case_id and e.rpt_process = '4a' and e.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess f on tbl.rpt_case_id = f.rpt_case_id and f.rpt_process = '5' and f.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess g on tbl.rpt_case_id = g.rpt_case_id and g.rpt_process = '5a' and g.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess h on tbl.rpt_case_id = h.rpt_case_id and h.rpt_process = '6' and h.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess i on tbl.rpt_case_id = i.rpt_case_id and i.rpt_process = '7' and i.rpt_date = tbl.rpt_date "
	
						+ "union "
	
						+ "select tbl.rpt_case_id, c_reference_no, c_type, tbl.rpt_matrix, tblaccessrole.role_name, tbluser.name, c_reseller_location, "
						+ "		a.rpt_status as status_1, b.rpt_status as status_2, c.rpt_status as status_3, d.rpt_status as status_4, e.rpt_status as status_4a, f.rpt_status as status_5, g.rpt_status as status_5a, h.rpt_status as status_6, i.rpt_status as status_7, "
						+ "		process_1, process_2, process_3, process_4, process_4a, process_5, process_5a, process_6, process_7, total_sla as current_sla, total_sla as end_to_end_sla, "
						+ "		(case when total_sla is not null and process_4a is not null then total_sla - process_4a else null end) as current_4a, "
						+ "		(case when total_sla is not null and process_5a is not null then total_sla - process_5a else null end) as current_5a "
						+ "from ( "
						+ "		select rpt_case_id, rpt_matrix, rpt_date, "
						+ "		sum(case when rpt_process = '1' then rpt_sla else null end) as process_1, "
						+ "		sum(case when rpt_process = '2' then rpt_sla else null end) as process_2, "
						+ "		sum(case when rpt_process = '3' then rpt_sla else null end) as process_3, "
						+ "		sum(case when rpt_process = '4' then rpt_sla else null end) as process_4, "
						+ "		sum(case when rpt_process = '4a' then rpt_sla else null end) as process_4a, "
						+ "		sum(case when rpt_process = '5' then rpt_sla else null end) as process_5, "
						+ "		sum(case when rpt_process = '5a' then rpt_sla else null end) as process_5a, "
						+ "		sum(case when rpt_process = '6' then rpt_sla else null end) as process_6, "
						+ "		sum(case when rpt_process = '7' then rpt_sla else null end) as process_7, "
						+ "		sum(rpt_sla) as total_sla "
						+ "		from rptslaprocess "
						+ "		join casemast on rpt_case_id = c_id and trunc(c_status_date) = trunc(rpt_date) "
						+ "		where c_status like 'CLOSE%' "
						+ "		and c_status_date >= to_date(?, 'DD/MM/YYYY') and c_status_date <= to_date(?, 'DD/MM/YYYY') "
						+ q_search
						+ "		group by rpt_case_id, rpt_matrix, rpt_date "
						+ "		order by rpt_case_id asc "
						+ ") tbl "
						+ "join casemast on rpt_case_id = c_id "
						+ "join tblaccessrole on role_code = c_group_code "
						+ "join tbluser on login_id = c_created_by "
						+ "left join rptslaprocess a on tbl.rpt_case_id = a.rpt_case_id and a.rpt_process = '1' and a.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess b on tbl.rpt_case_id = b.rpt_case_id and b.rpt_process = '2' and b.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess c on tbl.rpt_case_id = c.rpt_case_id and c.rpt_process = '3' and c.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess d on tbl.rpt_case_id = d.rpt_case_id and d.rpt_process = '4' and d.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess e on tbl.rpt_case_id = e.rpt_case_id and e.rpt_process = '4a' and e.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess f on tbl.rpt_case_id = f.rpt_case_id and f.rpt_process = '5' and f.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess g on tbl.rpt_case_id = g.rpt_case_id and g.rpt_process = '5a' and g.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess h on tbl.rpt_case_id = h.rpt_case_id and h.rpt_process = '6' and h.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess i on tbl.rpt_case_id = i.rpt_case_id and i.rpt_process = '7' and i.rpt_date = tbl.rpt_date ";
				
				PreparedStatement statement = conn.prepareStatement(sql);
			
				try {
					//System.out.println(sql);
					
					int last_index = 0;
	
	
					statement.setString(++last_index, search.get("to_date"));
					
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							statement.setString(++last_index, search.get("group_code"));
						}
						
						if(search.containsKey("group_user") && search.get("group_user").length() > 0){
							statement.setString(++last_index, search.get("group_user"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							statement.setString(++last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							statement.setString(++last_index, search.get("reseller_location"));
						}
					}
					
					statement.setString(++last_index, search.get("from_date"));
	
					statement.setString(++last_index, search.get("to_date"));
					
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							statement.setString(++last_index, search.get("group_code"));
						}
						
						if(search.containsKey("group_user") && search.get("group_user").length() > 0){
							statement.setString(++last_index, search.get("group_user"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							statement.setString(++last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							statement.setString(++last_index, search.get("reseller_location"));
						}
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							TreeMap<String, String> rRow = new TreeMap<String, String>();
							rRow.put("reference_no", resultSet.getString("c_reference_no"));
							rRow.put("type", resultSet.getString("c_type"));
							rRow.put("matrix", resultSet.getString("rpt_matrix"));
							rRow.put("group_name", resultSet.getString("role_name"));
							rRow.put("creator", resultSet.getString("name"));
							rRow.put("region", resultSet.getString("c_reseller_location"));
							rRow.put("process_1", resultSet.getString("process_1"));
							rRow.put("process_2", resultSet.getString("process_2"));
							rRow.put("process_3", resultSet.getString("process_3"));
							rRow.put("process_4", resultSet.getString("process_4"));
							rRow.put("process_4a", resultSet.getString("process_4a"));
							rRow.put("process_5", resultSet.getString("process_5"));
							rRow.put("process_5a", resultSet.getString("process_5a"));
							rRow.put("process_6", resultSet.getString("process_6"));
							rRow.put("process_7", resultSet.getString("process_7"));
							rRow.put("current_sla", resultSet.getString("current_sla"));
							rRow.put("end_to_end_sla", resultSet.getString("end_to_end_sla"));
							rRow.put("current_4a", resultSet.getString("current_4a"));
							rRow.put("current_5a", resultSet.getString("current_5a"));
							rRow.put("status_1", resultSet.getString("status_1"));
							rRow.put("status_2", resultSet.getString("status_2"));
							rRow.put("status_3", resultSet.getString("status_3"));
							rRow.put("status_4", resultSet.getString("status_4"));
							rRow.put("status_4a", resultSet.getString("status_4a"));
							rRow.put("status_5", resultSet.getString("status_5"));
							rRow.put("status_5a", resultSet.getString("status_5a"));
							rRow.put("status_6", resultSet.getString("status_6"));
							rRow.put("status_7", resultSet.getString("status_7"));
							
							rArr.add(rRow);
						}
					
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
				
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return rArr;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static TreeMap<String, String> getSlaProcessSummary(TreeMap<String, String> search, int sla_days) {
		TreeMap<String, String> rRow = new TreeMap<String, String>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
				
				if(search != null) {
					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						q_search += "and c_group_code = ? ";
					}
					
					if(search.containsKey("group_user") && search.get("group_user").length() > 0){
						q_search += "and c_created_by = ? ";
					}
					
					if(search.containsKey("application_type") && search.get("application_type").length() > 0){
						q_search += "and c_type = ? ";
					}
					
					if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
						q_search += "and c_reseller_location = ? ";
					}
				}
				
				
				String sql = "select sum(case when completed is not null then completed else 0 end) as completed_total, "
						+ "		sum(case when pending is not null then pending else 0 end) as pending_total, "
						+ "		sum(case when unsuccessful is not null then unsuccessful else 0 end) as unsuccessful_total, "
						+ "		sum(case when completed_1 is not null then completed_1 else 0 end) as completed_1_total, "
						+ "		sum(case when completed_2 is not null then completed_2 else 0 end) as completed_2_total "
						+ "from ( "
						+ "select 0, "
						+ "		sum(case when rpt_last_status like 'CLOSED - INSTALLATION & TRAINING COMPLETED%' then 1 else 0 end) as completed, "
						+ "		sum(case when rpt_last_status not like 'CLOSED%' then 1 else 0 end) as pending, "
						+ "		sum(case when rpt_last_status like 'CLOSED%' and rpt_last_status not like 'CLOSED - INSTALLATION and TRAINING COMPLETED%' then 1 else 0 end) as unsuccessful, "
						+ "		sum(case when rpt_last_status like 'CLOSED - INSTALLATION & TRAINING COMPLETED%' and total_sla <= ? then 1 else 0 end) as completed_1, "
						+ "		sum(case when rpt_last_status like 'CLOSED - INSTALLATION & TRAINING COMPLETED%' and total_sla > ? then 1 else 0 end) as completed_2 "
						+ "from ( "
						+ "		select rpt_case_id, rpt_last_status, sum(rpt_sla) as total_sla "
						+ "		from rptslaprocess "
						+ "		join casemast on rpt_case_id = c_id "
						+ "		where c_status not like 'CLOSE%' "
						+ "		and rpt_date = to_date(?, 'DD/MM/YYYY') "
						+ q_search
						+ "		group by rpt_case_id, rpt_last_status "
						+ "		order by rpt_case_id asc "
						+ ") a "
	
						+ "union "
	
						+ "select 1, "
						+ "		sum(case when rpt_last_status like 'CLOSED - INSTALLATION & TRAINING COMPLETED%' then 1 else 0 end) as completed, "
						+ "		sum(case when rpt_last_status not like 'CLOSED%' then 1 else 0 end) as pending, "
						+ "		sum(case when rpt_last_status like 'CLOSED%' and rpt_last_status not like 'CLOSED - INSTALLATION and TRAINING COMPLETED%' then 1 else 0 end) as unsuccessful, "
						+ "		sum(case when rpt_last_status like 'CLOSED - INSTALLATION & TRAINING COMPLETED%' and total_sla <= ? then 1 else 0 end) as completed_1, "
						+ "		sum(case when rpt_last_status like 'CLOSED - INSTALLATION & TRAINING COMPLETED%' and total_sla > ? then 1 else 0 end) as completed_2 "
						+ "from ( "
						+ "		select rpt_case_id, rpt_last_status, sum(rpt_sla) as total_sla "
						+ "		from rptslaprocess "
						+ "		join casemast on rpt_case_id = c_id and trunc(c_status_date) = trunc(rpt_date) "
						+ "		where c_status like 'CLOSE%' "
						+ "		and c_status_date >= to_date(?, 'DD/MM/YYYY') and c_status_date <= to_date(?, 'DD/MM/YYYY') "
						+ q_search
						+ "		group by rpt_case_id, rpt_last_status "
						+ "		order by rpt_case_id asc "
						+ ") b "
						+ ") c ";
				
				PreparedStatement statement = conn.prepareStatement(sql);
			
				try {
					//System.out.println(sql);
					
					int last_index = 0;
					statement.setInt(++last_index, sla_days);
					statement.setInt(++last_index, sla_days);
					statement.setString(++last_index, search.get("to_date"));
	
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							statement.setString(++last_index, search.get("group_code"));
						}
						
						if(search.containsKey("group_user") && search.get("group_user").length() > 0){
							statement.setString(++last_index, search.get("group_user"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							statement.setString(++last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							statement.setString(++last_index, search.get("reseller_location"));
						}
					}
					
					statement.setInt(++last_index, sla_days);
					statement.setInt(++last_index, sla_days);
					statement.setString(++last_index, search.get("from_date"));
					statement.setString(++last_index, search.get("to_date"));
					
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							statement.setString(++last_index, search.get("group_code"));
						}
						
						if(search.containsKey("group_user") && search.get("group_user").length() > 0){
							statement.setString(++last_index, search.get("group_user"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							statement.setString(++last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							statement.setString(++last_index, search.get("reseller_location"));
						}
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						if(resultSet.next()){
							rRow.put("completed", resultSet.getString("completed_total"));
							rRow.put("pending", resultSet.getString("pending_total"));
							rRow.put("unsuccessful", resultSet.getString("unsuccessful_total"));
							rRow.put("completed_1", resultSet.getString("completed_1_total"));
							rRow.put("completed_2", resultSet.getString("completed_2_total"));
						}
					
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
	
			return rRow;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static TreeMap<String, String> getSlaProcessSummary(TreeMap<String, String> search, int sla_days, String matrixType) {
		TreeMap<String, String> rRow = new TreeMap<String, String>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
				
				if(search != null) {
					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						q_search += "and c_group_code = ? ";
					}
					
					if(search.containsKey("group_user") && search.get("group_user").length() > 0){
						q_search += "and c_created_by = ? ";
					}
					
					if(search.containsKey("application_type") && search.get("application_type").length() > 0){
						q_search += "and c_type = ? ";
					}
					
					if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
						q_search += "and c_reseller_location = ? ";
					}
				}
				
				String matrix_Search = "";
				if(StringUtils.isNotBlank(matrixType)){
					matrix_Search = " and rpt_matrix = ? ";
				}else{
					matrix_Search = " and rpt_matrix is NULL ";
				}
				
				String sql = "select sum(case when completed is not null then completed else 0 end) as completed_total, "
						+ "		sum(case when pending is not null then pending else 0 end) as pending_total, "
						+ "		sum(case when unsuccessful is not null then unsuccessful else 0 end) as unsuccessful_total, "
						+ "		sum(case when completed_1_KV is not null then completed_1_KV else 0 end) as completed_1_KV_total, "
						+ "		sum(case when completed_1_OS is not null then completed_1_OS else 0 end) as completed_1_OS_total, "
						+ "		sum(case when completed_2_KV is not null then completed_2_KV else 0 end) as completed_2_KV_total, "
						+ "		sum(case when completed_2_OS is not null then completed_2_OS else 0 end) as completed_2_OS_total "
						+ "from ( "
						+ "select 0, "
						+ "		sum(case when rpt_last_status like 'CLOSED - INSTALLATION & TRAINING COMPLETED%' then 1 else 0 end) as completed, "
						+ "		sum(case when rpt_last_status not like 'CLOSED%' then 1 else 0 end) as pending, "
						+ "		sum(case when rpt_last_status like 'CLOSED%' and rpt_last_status not like 'CLOSED - INSTALLATION and TRAINING COMPLETED%' then 1 else 0 end) as unsuccessful, "
						+ "		sum(case when rpt_last_status like 'CLOSED - INSTALLATION & TRAINING COMPLETED%' and total_sla <= ? and (role_name like '%HQ%' and role_name != 'RT Maxis HQ') then 1 else 0 end) as completed_1_KV, "
						+ "		sum(case when rpt_last_status like 'CLOSED - INSTALLATION & TRAINING COMPLETED%' and total_sla > ? and (role_name like '%HQ%' and role_name != 'RT Maxis HQ') then 1 else 0 end) as completed_2_KV, "
						+ "		sum(case when rpt_last_status like 'CLOSED - INSTALLATION & TRAINING COMPLETED%' and total_sla <= ? and (role_name  not like '%HQ%' or role_name = 'RT Maxis HQ') then 1 else 0 end) as completed_1_OS, "
						+ "		sum(case when rpt_last_status like 'CLOSED - INSTALLATION & TRAINING COMPLETED%' and total_sla > ? and (role_name  not like '%HQ%' or role_name = 'RT Maxis HQ') then 1 else 0 end) as completed_2_OS "
						+ "from ( "
						+ "		select rpt_case_id, rpt_last_status, sum(rpt_sla) as total_sla, tblaccessrole.role_name "
						+ "		from rptslaprocess "
						+ "		join casemast on rpt_case_id = c_id "
						+ " 	join tblaccessrole on role_code = c_group_code "
						+ "		where c_status not like 'CLOSE%' "
						+ "		and rpt_date = to_date(?, 'DD/MM/YYYY') "
						+ matrix_Search
						+ q_search
						+ "		group by rpt_case_id, rpt_last_status, role_name "
						+ "		order by rpt_case_id asc "
						+ ") a "
	
						+ "union "
	
						+ "select 1, "
						+ "		sum(case when rpt_last_status like 'CLOSED - INSTALLATION & TRAINING COMPLETED%' then 1 else 0 end) as completed, "
						+ "		sum(case when rpt_last_status not like 'CLOSED%' then 1 else 0 end) as pending, "
						+ "		sum(case when rpt_last_status like 'CLOSED%' and rpt_last_status not like 'CLOSED - INSTALLATION and TRAINING COMPLETED%' then 1 else 0 end) as unsuccessful, "
						+ "		sum(case when rpt_last_status like 'CLOSED - INSTALLATION & TRAINING COMPLETED%' and total_sla <= ? and (role_name like '%HQ%' and role_name != 'RT Maxis HQ') then 1 else 0 end) as completed_1_KV, "
						+ "		sum(case when rpt_last_status like 'CLOSED - INSTALLATION & TRAINING COMPLETED%' and total_sla > ? and (role_name like '%HQ%' and role_name != 'RT Maxis HQ') then 1 else 0 end) as completed_2_KV, "
						+ "		sum(case when rpt_last_status like 'CLOSED - INSTALLATION & TRAINING COMPLETED%' and total_sla <= ? and (role_name  not like '%HQ%' or role_name = 'RT Maxis HQ') then 1 else 0 end) as completed_1_OS, "
						+ "		sum(case when rpt_last_status like 'CLOSED - INSTALLATION & TRAINING COMPLETED%' and total_sla > ? and (role_name  not like '%HQ%' or role_name = 'RT Maxis HQ') then 1 else 0 end) as completed_2_OS "
						+ "from ( "
						+ "		select rpt_case_id, rpt_last_status, sum(rpt_sla) as total_sla, tblaccessrole.role_name "
						+ "		from rptslaprocess "
						+ "		join casemast on rpt_case_id = c_id and trunc(c_status_date) = trunc(rpt_date) "
						+ " 	join tblaccessrole on role_code = c_group_code "
						+ "		where c_status like 'CLOSE%' "
						+ "		and c_status_date >= to_date(?, 'DD/MM/YYYY') and c_status_date <= to_date(?, 'DD/MM/YYYY') "
						+ matrix_Search
						+ q_search
						+ "		group by rpt_case_id, rpt_last_status, role_name "
						+ "		order by rpt_case_id asc "
						+ ") b "
						+ ") c ";
				
				PreparedStatement statement = conn.prepareStatement(sql);
			
				try {
					//System.out.println(sql);
					
					int last_index = 0;
					statement.setInt(++last_index, sla_days);
					statement.setInt(++last_index, sla_days);
					statement.setInt(++last_index, sla_days);
					statement.setInt(++last_index, sla_days);
					statement.setString(++last_index, search.get("to_date"));
					
					if(StringUtils.isNotBlank(matrixType)){
						statement.setString(++last_index,matrixType);
					}
					
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							statement.setString(++last_index, search.get("group_code"));
						}
						
						if(search.containsKey("group_user") && search.get("group_user").length() > 0){
							statement.setString(++last_index, search.get("group_user"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							statement.setString(++last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							statement.setString(++last_index, search.get("reseller_location"));
						}
					}
					
					statement.setInt(++last_index, sla_days);
					statement.setInt(++last_index, sla_days);
					statement.setInt(++last_index, sla_days);
					statement.setInt(++last_index, sla_days);
					statement.setString(++last_index, search.get("from_date"));
					statement.setString(++last_index, search.get("to_date"));
					
					if(StringUtils.isNotBlank(matrixType)){
						statement.setString(++last_index,matrixType);
					}
					
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							statement.setString(++last_index, search.get("group_code"));
						}
						
						if(search.containsKey("group_user") && search.get("group_user").length() > 0){
							statement.setString(++last_index, search.get("group_user"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							statement.setString(++last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							statement.setString(++last_index, search.get("reseller_location"));
						}
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						if(resultSet.next()){
							rRow.put("completed", resultSet.getString("completed_total"));
							rRow.put("pending", resultSet.getString("pending_total"));
							rRow.put("unsuccessful", resultSet.getString("unsuccessful_total"));
							rRow.put("completed_1_KV", resultSet.getString("completed_1_KV_total"));
							rRow.put("completed_1_OS", resultSet.getString("completed_1_OS_total"));
							rRow.put("completed_2_KV", resultSet.getString("completed_2_KV_total"));
							rRow.put("completed_2_OS", resultSet.getString("completed_2_OS_total"));
							rRow.put("total", String.valueOf((resultSet.getInt("completed_total") + resultSet.getInt("pending_total") + resultSet.getInt("unsuccessful_total"))));
						}
					
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
	
			return rRow;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	
	public static ArrayList<TreeMap<String, String>> getExternalSlaProcess(TreeMap<String, String> search) {
		ArrayList<TreeMap<String, String>> rArr = new ArrayList<TreeMap<String, String>>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
				
				if(search != null) {
					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						q_search += "and c_group_code = ? ";
					}
					
					if(search.containsKey("group_user") && search.get("group_user").length() > 0){
						q_search += "and c_created_by = ? ";
					}
					
					if(search.containsKey("application_type") && search.get("application_type").length() > 0){
						q_search += "and c_type = ? ";
					}
					
					if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
						q_search += "and c_reseller_location = ? ";
					}
				}
				
				
	
				String sql = "select tbl.rpt_case_id, c_reference_no, c_type, tbl.rpt_matrix, tblaccessrole.role_name, tbluser.name, c_reseller_location, "
						+ "		a.rpt_status as status_1, b.rpt_status as status_2, c.rpt_status as status_3, d.rpt_status as status_4, e.rpt_status as status_4a, f.rpt_status as status_5, g.rpt_status as status_5a, h.rpt_status as status_6, i.rpt_status as status_7, "
						+ "		process_1, process_2, process_3, process_4, process_4a, process_5, process_5a, process_6, process_7, total_sla as current_sla, null as end_to_end_sla, "
						+ "		(case when total_sla is not null and process_4a is not null then total_sla - process_4a else null end) as current_4a, "
						+ "		(case when total_sla is not null and process_5a is not null then total_sla - process_5a else null end) as current_5a "
						+ "from ( "
						+ "		select rpt_case_id, rpt_matrix, rpt_date, "
						+ "		sum(case when rpt_process = '1' then rpt_sla else null end) as process_1, "
						+ "		sum(case when rpt_process = '2' then rpt_sla else null end) as process_2, "
						+ "		sum(case when rpt_process = '3' then rpt_sla else null end) as process_3, "
						+ "		sum(case when rpt_process = '4' then rpt_sla else null end) as process_4, "
						+ "		sum(case when rpt_process = '4a' then rpt_sla else null end) as process_4a, "
						+ "		sum(case when rpt_process = '5' then rpt_sla else null end) as process_5, "
						+ "		sum(case when rpt_process = '5a' then rpt_sla else null end) as process_5a, "
						+ "		sum(case when rpt_process = '6' then rpt_sla else null end) as process_6, "
						+ "		sum(case when rpt_process = '7' then rpt_sla else null end) as process_7, "
						+ "		sum(rpt_sla) as total_sla "
						+ "		from rptslaprocess "
						+ "		join casemast on rpt_case_id = c_id "
						+ "		where c_status not like 'CLOSE%' "
						+ "		and rpt_date = to_date(?, 'DD/MM/YYYY') "
						+ q_search
						+ "		group by rpt_case_id, rpt_matrix, rpt_date "
						+ "		order by rpt_case_id asc "
						+ ") tbl "
						+ "join casemast on rpt_case_id = c_id "
						+ "join tblaccessrole on role_code = c_group_code "
						+ "join tbluser on login_id = c_created_by "
						+ "left join rptslaprocess a on tbl.rpt_case_id = a.rpt_case_id and a.rpt_process = '1' and a.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess b on tbl.rpt_case_id = b.rpt_case_id and b.rpt_process = '2' and b.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess c on tbl.rpt_case_id = c.rpt_case_id and c.rpt_process = '3' and c.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess d on tbl.rpt_case_id = d.rpt_case_id and d.rpt_process = '4' and d.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess e on tbl.rpt_case_id = e.rpt_case_id and e.rpt_process = '4a' and e.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess f on tbl.rpt_case_id = f.rpt_case_id and f.rpt_process = '5' and f.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess g on tbl.rpt_case_id = g.rpt_case_id and g.rpt_process = '5a' and g.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess h on tbl.rpt_case_id = h.rpt_case_id and h.rpt_process = '6' and h.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess i on tbl.rpt_case_id = i.rpt_case_id and i.rpt_process = '7' and i.rpt_date = tbl.rpt_date "
	
						+ "union "
	
						+ "select tbl.rpt_case_id, c_reference_no, c_type, tbl.rpt_matrix, tblaccessrole.role_name, tbluser.name, c_reseller_location, "
						+ "		a.rpt_status as status_1, b.rpt_status as status_2, c.rpt_status as status_3, d.rpt_status as status_4, e.rpt_status as status_4a, f.rpt_status as status_5, g.rpt_status as status_5a, h.rpt_status as status_6, i.rpt_status as status_7, "
						+ "		process_1, process_2, process_3, process_4, process_4a, process_5, process_5a, process_6, process_7, total_sla as current_sla, total_sla as end_to_end_sla, "
						+ "		(case when total_sla is not null and process_4a is not null then total_sla - process_4a else null end) as current_4a, "
						+ "		(case when total_sla is not null and process_5a is not null then total_sla - process_5a else null end) as current_5a "
						+ "from ( "
						+ "		select rpt_case_id, rpt_matrix, rpt_date, "
						+ "		sum(case when rpt_process = '1' then rpt_sla else null end) as process_1, "
						+ "		sum(case when rpt_process = '2' then rpt_sla else null end) as process_2, "
						+ "		sum(case when rpt_process = '3' then rpt_sla else null end) as process_3, "
						+ "		sum(case when rpt_process = '4' then rpt_sla else null end) as process_4, "
						+ "		sum(case when rpt_process = '4a' then rpt_sla else null end) as process_4a, "
						+ "		sum(case when rpt_process = '5' then rpt_sla else null end) as process_5, "
						+ "		sum(case when rpt_process = '5a' then rpt_sla else null end) as process_5a, "
						+ "		sum(case when rpt_process = '6' then rpt_sla else null end) as process_6, "
						+ "		sum(case when rpt_process = '7' then rpt_sla else null end) as process_7, "
						+ "		sum(rpt_sla) as total_sla "
						+ "		from rptslaprocess "
						+ "		join casemast on rpt_case_id = c_id and trunc(c_status_date) = trunc(rpt_date) "
						+ "		where c_status like 'CLOSE%' "
						+ "		and c_status_date >= to_date(?, 'DD/MM/YYYY') and c_status_date <= to_date(?, 'DD/MM/YYYY') "
						+ q_search
						+ "		group by rpt_case_id, rpt_matrix, rpt_date "
						+ "		order by rpt_case_id asc "
						+ ") tbl "
						+ "join casemast on rpt_case_id = c_id "
						+ "join tblaccessrole on role_code = c_group_code "
						+ "join tbluser on login_id = c_created_by "
						+ "left join rptslaprocess a on tbl.rpt_case_id = a.rpt_case_id and a.rpt_process = '1' and a.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess b on tbl.rpt_case_id = b.rpt_case_id and b.rpt_process = '2' and b.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess c on tbl.rpt_case_id = c.rpt_case_id and c.rpt_process = '3' and c.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess d on tbl.rpt_case_id = d.rpt_case_id and d.rpt_process = '4' and d.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess e on tbl.rpt_case_id = e.rpt_case_id and e.rpt_process = '4a' and e.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess f on tbl.rpt_case_id = f.rpt_case_id and f.rpt_process = '5' and f.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess g on tbl.rpt_case_id = g.rpt_case_id and g.rpt_process = '5a' and g.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess h on tbl.rpt_case_id = h.rpt_case_id and h.rpt_process = '6' and h.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess i on tbl.rpt_case_id = i.rpt_case_id and i.rpt_process = '7' and i.rpt_date = tbl.rpt_date ";
				
				PreparedStatement statement = conn.prepareStatement(sql);
			
				try {
					//System.out.println(sql);
					
					int last_index = 0;
	
	
					statement.setString(++last_index, search.get("to_date"));
					
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							statement.setString(++last_index, search.get("group_code"));
						}
						
						if(search.containsKey("group_user") && search.get("group_user").length() > 0){
							statement.setString(++last_index, search.get("group_user"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							statement.setString(++last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							statement.setString(++last_index, search.get("reseller_location"));
						}
					}
					
					statement.setString(++last_index, search.get("from_date"));
	
					statement.setString(++last_index, search.get("to_date"));
					
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							statement.setString(++last_index, search.get("group_code"));
						}
						
						if(search.containsKey("group_user") && search.get("group_user").length() > 0){
							statement.setString(++last_index, search.get("group_user"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							statement.setString(++last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							statement.setString(++last_index, search.get("reseller_location"));
						}
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							TreeMap<String, String> rRow = new TreeMap<String, String>();
							rRow.put("reference_no", resultSet.getString("c_reference_no"));
							rRow.put("type", resultSet.getString("c_type"));
							rRow.put("matrix", resultSet.getString("rpt_matrix"));
							rRow.put("group_name", resultSet.getString("role_name"));
							rRow.put("creator", resultSet.getString("name"));
							rRow.put("region", resultSet.getString("c_reseller_location"));
							rRow.put("process_1", resultSet.getString("process_1"));
							rRow.put("process_2", resultSet.getString("process_2"));
							rRow.put("process_3", resultSet.getString("process_3"));
							rRow.put("process_4", resultSet.getString("process_4"));
							rRow.put("process_4a", resultSet.getString("process_4a"));
							rRow.put("process_5", resultSet.getString("process_5"));
							rRow.put("process_5a", resultSet.getString("process_5a"));
							rRow.put("process_6", resultSet.getString("process_6"));
							rRow.put("process_7", resultSet.getString("process_7"));
							rRow.put("current_sla", resultSet.getString("current_sla"));
							rRow.put("end_to_end_sla", resultSet.getString("end_to_end_sla"));
							rRow.put("current_4a", resultSet.getString("current_4a"));
							rRow.put("current_5a", resultSet.getString("current_5a"));
							rRow.put("status_1", resultSet.getString("status_1"));
							rRow.put("status_2", resultSet.getString("status_2"));
							rRow.put("status_3", resultSet.getString("status_3"));
							rRow.put("status_4", resultSet.getString("status_4"));
							rRow.put("status_4a", resultSet.getString("status_4a"));
							rRow.put("status_5", resultSet.getString("status_5"));
							rRow.put("status_5a", resultSet.getString("status_5a"));
							rRow.put("status_6", resultSet.getString("status_6"));
							rRow.put("status_7", resultSet.getString("status_7"));
							
							rArr.add(rRow);
						}
					
					} catch(Exception e){
						e.printStackTrace();
					}  finally {
						resultSet.close();
					}
					
				} catch(Exception e){
					e.printStackTrace();
				}  finally {
					statement.close();
				}
				
			} catch(Exception e){
				e.printStackTrace();
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return rArr;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static TreeMap<String, String> getExternalSlaProcessSummary(TreeMap<String, String> search, int sla_days) {
		TreeMap<String, String> rRow = new TreeMap<String, String>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
				
				if(search != null) {
					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						q_search += "and c_group_code = ? ";
					}
					
					if(search.containsKey("group_user") && search.get("group_user").length() > 0){
						q_search += "and c_created_by = ? ";
					}
					
					if(search.containsKey("application_type") && search.get("application_type").length() > 0){
						q_search += "and c_type = ? ";
					}
					
					if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
						q_search += "and c_reseller_location = ? ";
					}
				}
				
				
				String sql = "select sum(case when completed is not null then completed else 0 end) as completed_total, "
						+ "		sum(case when pending is not null then pending else 0 end) as pending_total, "
						+ "		sum(case when unsuccessful is not null then unsuccessful else 0 end) as unsuccessful_total, "
						+ "		sum(case when completed_1 is not null then completed_1 else 0 end) as completed_1_total, "
						+ "		sum(case when completed_2 is not null then completed_2 else 0 end) as completed_2_total "
						+ "from ( "
						+ "select 0, "
						+ "		sum(case when rpt_last_status like 'CLOSED - INSTALLATION & TRAINING COMPLETED%' then 1 else 0 end) as completed, "
						+ "		sum(case when rpt_last_status not like 'CLOSED%' then 1 else 0 end) as pending, "
						+ "		sum(case when rpt_last_status like 'CLOSED%' and rpt_last_status not like 'CLOSED - INSTALLATION & TRAINING COMPLETED%' then 1 else 0 end) as unsuccessful, "
						+ "		sum(case when rpt_last_status like 'CLOSED - INSTALLATION & TRAINING COMPLETED%' and total_sla <= ? then 1 else 0 end) as completed_1, "
						+ "		sum(case when rpt_last_status like 'CLOSED - INSTALLATION & TRAINING COMPLETED%' and total_sla > ? then 1 else 0 end) as completed_2 "
						+ "from ( "
						+ "		select rpt_case_id, rpt_last_status, sum(rpt_sla) as total_sla "
						+ "		from rptslaprocess "
						+ "		join casemast on rpt_case_id = c_id "
						+ "		where c_status not like 'CLOSE%' "
						+ "		and rpt_date = to_date(?, 'DD/MM/YYYY') "
						+ q_search
						+ "		group by rpt_case_id, rpt_last_status "
						+ "		order by rpt_case_id asc "
						+ ") a "
	
						+ "union "
	
						+ "select 1, "
						+ "		sum(case when rpt_last_status like 'CLOSED - INSTALLATION & TRAINING COMPLETED%' then 1 else 0 end) as completed, "
						+ "		sum(case when rpt_last_status not like 'CLOSED%' then 1 else 0 end) as pending, "
						+ "		sum(case when rpt_last_status like 'CLOSED%' and rpt_last_status not like 'CLOSED - INSTALLATION & TRAINING COMPLETED%' then 1 else 0 end) as unsuccessful, "
						+ "		sum(case when rpt_last_status like 'CLOSED - INSTALLATION & TRAINING COMPLETED%' and total_sla <= ? then 1 else 0 end) as completed_1, "
						+ "		sum(case when rpt_last_status like 'CLOSED - INSTALLATION & TRAINING COMPLETED%' and total_sla > ? then 1 else 0 end) as completed_2 "
						+ "from ( "
						+ "		select rpt_case_id, rpt_last_status, sum(rpt_sla) as total_sla "
						+ "		from rptslaprocess "
						+ "		join casemast on rpt_case_id = c_id and trunc(c_status_date) = trunc(rpt_date) "
						+ "		where c_status like 'CLOSE%' "
						+ "		and c_status_date >= to_date(?, 'DD/MM/YYYY') and c_status_date <= to_date(?, 'DD/MM/YYYY') "
						+ q_search
						+ "		group by rpt_case_id, rpt_last_status "
						+ "		order by rpt_case_id asc "
						+ ") b "
						+ ") c ";
				
				PreparedStatement statement = conn.prepareStatement(sql);
			
				try {
					//System.out.println(sql);
					
					int last_index = 0;
					statement.setInt(++last_index, sla_days);
					statement.setInt(++last_index, sla_days);
					statement.setString(++last_index, search.get("to_date"));
	
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							statement.setString(++last_index, search.get("group_code"));
						}
						
						if(search.containsKey("group_user") && search.get("group_user").length() > 0){
							statement.setString(++last_index, search.get("group_user"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							statement.setString(++last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							statement.setString(++last_index, search.get("reseller_location"));
						}
					}
					
					statement.setInt(++last_index, sla_days);
					statement.setInt(++last_index, sla_days);
					statement.setString(++last_index, search.get("from_date"));
					statement.setString(++last_index, search.get("to_date"));
					
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							statement.setString(++last_index, search.get("group_code"));
						}
						
						if(search.containsKey("group_user") && search.get("group_user").length() > 0){
							statement.setString(++last_index, search.get("group_user"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							statement.setString(++last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							statement.setString(++last_index, search.get("reseller_location"));
						}
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						if(resultSet.next()){
							rRow.put("completed", resultSet.getString("completed_total"));
							rRow.put("pending", resultSet.getString("pending_total"));
							rRow.put("unsuccessful", resultSet.getString("unsuccessful_total"));
							rRow.put("completed_1", resultSet.getString("completed_1_total"));
							rRow.put("completed_2", resultSet.getString("completed_2_total"));
						}
					
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return rRow;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static ArrayList<TreeMap<String, String>> getRegionSla(TreeMap<String, String> search) {
		ArrayList<TreeMap<String, String>> rArr = new ArrayList<TreeMap<String, String>>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
				
				if(search != null) {
					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						q_search += "and c_group_code = ? ";
					}
					
					if(search.containsKey("group_user") && search.get("group_user").length() > 0){
						q_search += "and c_created_by = ? ";
					}
					
					if(search.containsKey("application_type") && search.get("application_type").length() > 0){
						q_search += "and c_type = ? ";
					}
					
					if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
						q_search += "and c_reseller_location = ? ";
					}
				}
				
	
				String sql = "select tbl.rpt_case_id, c_reference_no, c_type, tbl.rpt_matrix, tblaccessrole.role_name, tbluser.name, c_reseller_location, "
						+ "		a.rpt_status as status_1, b.rpt_status as status_2, c.rpt_status as status_3, d.rpt_status as status_4, e.rpt_status as status_4a, f.rpt_status as status_5, g.rpt_status as status_5a, h.rpt_status as status_6, i.rpt_status as status_7, "
						+ "		process_1_5, process_6, process_7, total_sla as current_sla, null as end_to_end_sla "
						+ "from ( "
						+ "		select rpt_case_id, rpt_matrix, rpt_date, "
						+ "		sum(case when rpt_process = '1' or rpt_process = '2' or rpt_process = '3' or rpt_process = '4' or rpt_process = '4a' or rpt_process = '5' or rpt_process = '5a' then rpt_sla else null end) as process_1_5, "
						+ "		sum(case when rpt_process = '6' then rpt_sla else null end) as process_6, "
						+ "		sum(case when rpt_process = '7' then rpt_sla else null end) as process_7, "
						+ "		sum(rpt_sla) as total_sla "
						+ "		from rptslaprocess "
						+ "		join casemast on rpt_case_id = c_id "
						+ "		where rpt_status not like 'CLOSE%' "
						+ "		and c_country_code != 'MY'"
						+ "		and rpt_date = to_date(?, 'DD/MM/YYYY') "
						+ q_search
						+ "		group by rpt_case_id, rpt_matrix, rpt_date "
						+ "		order by rpt_case_id asc "
						+ ") tbl "
						+ "join casemast on rpt_case_id = c_id "
						+ "join tblaccessrole on role_code = c_group_code "
						+ "join tbluser on login_id = c_created_by "
						+ "left join rptslaprocess a on tbl.rpt_case_id = a.rpt_case_id and a.rpt_process = '1' and a.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess b on tbl.rpt_case_id = b.rpt_case_id and b.rpt_process = '2' and b.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess c on tbl.rpt_case_id = c.rpt_case_id and c.rpt_process = '3' and c.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess d on tbl.rpt_case_id = d.rpt_case_id and d.rpt_process = '4' and d.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess e on tbl.rpt_case_id = e.rpt_case_id and e.rpt_process = '4a' and e.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess f on tbl.rpt_case_id = f.rpt_case_id and f.rpt_process = '5' and f.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess g on tbl.rpt_case_id = g.rpt_case_id and g.rpt_process = '5a' and g.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess h on tbl.rpt_case_id = h.rpt_case_id and h.rpt_process = '6' and h.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess i on tbl.rpt_case_id = i.rpt_case_id and i.rpt_process = '7' and i.rpt_date = tbl.rpt_date "
						
	
						+ "union "
	
						+ "select tbl.rpt_case_id, c_reference_no, c_type, tbl.rpt_matrix, tblaccessrole.role_name, tbluser.name, c_reseller_location, "
						+ "		a.rpt_status as status_1, b.rpt_status as status_2, c.rpt_status as status_3, d.rpt_status as status_4, e.rpt_status as status_4a, f.rpt_status as status_5, g.rpt_status as status_5a, h.rpt_status as status_6, i.rpt_status as status_7, "
						+ "		process_1_5, process_6, process_7, total_sla as current_sla, total_sla as end_to_end_sla "
						+ "from ( "
						+ "		select rpt_case_id, rpt_matrix, rpt_date, "
						+ "		sum(case when rpt_process = '1' or rpt_process = '2' or rpt_process = '3' or rpt_process = '4' or rpt_process = '4a' or rpt_process = '5' or rpt_process = '5a' then rpt_sla else null end) as process_1_5, "
						+ "		sum(case when rpt_process = '6' then rpt_sla else null end) as process_6, "
						+ "		sum(case when rpt_process = '7' then rpt_sla else null end) as process_7, "
						+ "		sum(rpt_sla) as total_sla "
						+ "		from rptslaprocess "
						+ "		join casemast on rpt_case_id = c_id and trunc(c_status_date) = trunc(rpt_date) "
						+ "		where rpt_status like 'CLOSE%' "
						+ "		and c_country_code != 'MY'"
						+ "		and c_status_date >= to_date(?, 'DD/MM/YYYY') and c_status_date <= to_date(?, 'DD/MM/YYYY') "
						+ q_search
						+ "		group by rpt_case_id, rpt_matrix, rpt_date "
						+ "		order by rpt_case_id asc "
						+ ") tbl "
						+ "join casemast on rpt_case_id = c_id "
						+ "join tblaccessrole on role_code = c_group_code "
						+ "join tbluser on login_id = c_created_by "
						+ "left join rptslaprocess a on tbl.rpt_case_id = a.rpt_case_id and a.rpt_process = '1' and a.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess b on tbl.rpt_case_id = b.rpt_case_id and b.rpt_process = '2' and b.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess c on tbl.rpt_case_id = c.rpt_case_id and c.rpt_process = '3' and c.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess d on tbl.rpt_case_id = d.rpt_case_id and d.rpt_process = '4' and d.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess e on tbl.rpt_case_id = e.rpt_case_id and e.rpt_process = '4a' and e.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess f on tbl.rpt_case_id = f.rpt_case_id and f.rpt_process = '5' and f.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess g on tbl.rpt_case_id = g.rpt_case_id and g.rpt_process = '5a' and g.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess h on tbl.rpt_case_id = h.rpt_case_id and h.rpt_process = '6' and h.rpt_date = tbl.rpt_date "
						+ "left join rptslaprocess i on tbl.rpt_case_id = i.rpt_case_id and i.rpt_process = '7' and i.rpt_date = tbl.rpt_date ";
				
				PreparedStatement statement = conn.prepareStatement(sql);
			
				try {
					//System.out.println(sql);
					
					int last_index = 0;
	
	
					statement.setString(++last_index, search.get("to_date"));
					
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							statement.setString(++last_index, search.get("group_code"));
						}
						
						if(search.containsKey("group_user") && search.get("group_user").length() > 0){
							statement.setString(++last_index, search.get("group_user"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							statement.setString(++last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							statement.setString(++last_index, search.get("reseller_location"));
						}
					}
					
					statement.setString(++last_index, search.get("from_date"));
	
					statement.setString(++last_index, search.get("to_date"));
					
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							statement.setString(++last_index, search.get("group_code"));
						}
						
						if(search.containsKey("group_user") && search.get("group_user").length() > 0){
							statement.setString(++last_index, search.get("group_user"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							statement.setString(++last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							statement.setString(++last_index, search.get("reseller_location"));
						}
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							TreeMap<String, String> rRow = new TreeMap<String, String>();
							rRow.put("reference_no", resultSet.getString("c_reference_no"));
							rRow.put("type", resultSet.getString("c_type"));
							rRow.put("matrix", resultSet.getString("rpt_matrix"));
							rRow.put("group_name", resultSet.getString("role_name"));
							rRow.put("creator", resultSet.getString("name"));
							rRow.put("region", resultSet.getString("c_reseller_location"));
							rRow.put("process_1_5", resultSet.getString("process_1_5"));
							rRow.put("process_6", resultSet.getString("process_6"));
							rRow.put("process_7", resultSet.getString("process_7"));
							rRow.put("current_sla", resultSet.getString("current_sla"));
							rRow.put("end_to_end_sla", resultSet.getString("end_to_end_sla"));
							rRow.put("status_1", resultSet.getString("status_1"));
							rRow.put("status_2", resultSet.getString("status_2"));
							rRow.put("status_3", resultSet.getString("status_3"));
							rRow.put("status_4", resultSet.getString("status_4"));
							rRow.put("status_4a", resultSet.getString("status_4a"));
							rRow.put("status_5", resultSet.getString("status_5"));
							rRow.put("status_5a", resultSet.getString("status_5a"));
							rRow.put("status_6", resultSet.getString("status_6"));
							rRow.put("status_7", resultSet.getString("status_7"));
							
							rArr.add(rRow);
						}
					
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return rArr;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static ArrayList<TreeMap<String, String>> getRegionSlaSummary(TreeMap<String, String> search) {
		ArrayList<TreeMap<String, String>> rArr = new ArrayList<TreeMap<String, String>>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
				
				if(search != null) {
					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						q_search += "and c_group_code = ? ";
					}
					
					if(search.containsKey("group_user") && search.get("group_user").length() > 0){
						q_search += "and c_created_by = ? ";
					}
					
					if(search.containsKey("application_type") && search.get("application_type").length() > 0){
						q_search += "and c_type = ? ";
					}
					
					if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
						q_search += "and c_reseller_location = ? ";
					}
				}
				
				
				String sql = "select c_country_code, "
						+ "		count(rpt_case_id) as count "
						+ "from ( "
						+ "		select rpt_case_id, c_country_code "
						+ "		from rptslaprocess "
						+ "		join casemast on rpt_case_id = c_id "
						+ "		where c_status not like 'CLOSE%' "
						+ "		and c_country_code != 'MY' "
						+ "		and rpt_date = to_date(?, 'DD/MM/YYYY') "
						+ q_search
						+ "		group by rpt_case_id, c_country_code "
	
						+ "union "
	
						+ "		select rpt_case_id, c_country_code "
						+ "		from rptslaprocess "
						+ "		join casemast on rpt_case_id = c_id and trunc(c_status_date) = trunc(rpt_date) "
						+ "		where c_status like 'CLOSE%' "
						+ "		and c_country_code != 'MY' "
						+ "		and c_status_date >= to_date(?, 'DD/MM/YYYY') and c_status_date <= to_date(?, 'DD/MM/YYYY') "
						+ q_search
						+ "		group by rpt_case_id, c_country_code "
						+ ") tbl "
						+ "group by c_country_code ";
				
				PreparedStatement statement = conn.prepareStatement(sql);
			
				try {
					//System.out.println(sql);
					
					int last_index = 0;
					statement.setString(++last_index, search.get("to_date"));
	
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							statement.setString(++last_index, search.get("group_code"));
						}
						
						if(search.containsKey("group_user") && search.get("group_user").length() > 0){
							statement.setString(++last_index, search.get("group_user"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							statement.setString(++last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							statement.setString(++last_index, search.get("reseller_location"));
						}
					}
					
					statement.setString(++last_index, search.get("from_date"));
					statement.setString(++last_index, search.get("to_date"));
					
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							statement.setString(++last_index, search.get("group_code"));
						}
						
						if(search.containsKey("group_user") && search.get("group_user").length() > 0){
							statement.setString(++last_index, search.get("group_user"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							statement.setString(++last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							statement.setString(++last_index, search.get("reseller_location"));
						}
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							TreeMap<String, String> rRow = new TreeMap<String, String>();
							rRow.put("country_code", resultSet.getString("c_country_code"));
							rRow.put("count", resultSet.getString("count"));
							
							rArr.add(rRow);
						}
					
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return rArr;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static ArrayList<TreeMap<String, String>> getStatusReportMonthly(TreeMap<String, String> search) {
		ArrayList<TreeMap<String, String>> cArr = new ArrayList<TreeMap<String, String>>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
				
				if(search != null) {
					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						q_search += "and c_group_code = ? ";
					}
					
					if(search.containsKey("application_type") && search.get("application_type").length() > 0){
						q_search += "and c_type = ? ";
					}
					
					if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
						q_search += "and c_reseller_location = ? ";
					}
					
					if(search.containsKey("year") && search.get("year").length() > 0){
						q_search += "and rcs_year = ? ";
					}
					
					if(search.containsKey("month") && search.get("month").length() > 0){
						q_search += "and rcs_month = ? ";
					}
					
					if(search.containsKey("status") && search.get("status").length() > 0){
						q_search += "and rcs_status = ? ";
					}
				}
				
				String sql = "select rptcasestatus.*, casemast.*, tblaccessrole.role_name, tbluser.name "
							+ "(trunc(rcs_status_date) - c_created_date) as duration_days, rcs_sla "
							+ "from rptcasestatus "
							+ "left join casemast on rptcasestatus.rcs_case_id = casemast.c_id "
							+ "left join tblaccessrole on role_code = c_group_code " 
							+ "left join tbuser on rptcasestatus.rcs_case_id = tbluser.login_id "
							+ "where 1 = 1 "
							+ q_search;
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					
					int last_index = 0;
					
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("group_code"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("reseller_location"));
						}
						
						if(search.containsKey("year") && search.get("year").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("year"));
						}
						
						if(search.containsKey("month") && search.get("month").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("month"));
						}
						
						if(search.containsKey("status") && search.get("status").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("status"));
						}
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							
							TreeMap<String, String> c = new TreeMap<String, String>();
							
							int duration = resultSet.getInt("duration_days");
							long created_date = ((Timestamp) resultSet.getTimestamp("c_created_date")).getTime();
							
							if(resultSet.getTimestamp("c_submit_date") != null){
								created_date = ((Timestamp) resultSet.getTimestamp("c_submit_date")).getTime();
							}
							
							String crtd_date = Casemast.formatCreatedDate("dd/MM/yyyy HH:mm:ss", created_date);
							
							c.put("map_ref_no", resultSet.getString("c_reference_no"));
							c.put("application_type", resultSet.getString("c_type"));
							c.put("created_date", crtd_date);
							c.put("matrix", resultSet.getString("rcs_matrix"));
							c.put("group_code", resultSet.getString("role_name") == null ? resultSet.getString("c_group_code") : resultSet.getString("role_name"));
							c.put("creator", StringUtils.isNotBlank(resultSet.getString("name"))? resultSet.getString("name"): resultSet.getString("c_created_by"));
							c.put("region", resultSet.getString("c_reseller_location"));
							c.put("status", resultSet.getString("rcs_status"));
							//c.put("duration", Integer.toString(duration));
							c.put("duration", resultSet.getString("rcs_sla"));
							cArr.add(c);
						}
						
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
				
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return cArr;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static ArrayList<TreeMap<String, String>> getStatusReportDaily(TreeMap<String, String> search) {
		ArrayList<TreeMap<String, String>> cArr = new ArrayList<TreeMap<String, String>>();
		
		/*String startStatus = CasemastFactory.getStartStatus(1);
		if(startStatus == null){
			return null;
		}
		
		String[] startArr = startStatus.split(",");
		String paramStr = "";
		String paramSep = "";
		
		for(String s : startArr) {
			paramStr += paramSep + "?";
			paramSep = ", ";
		}*/
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
				String q_search2 = "";
				
				if(search != null) {
					
					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						q_search += "and c_group_code = ? ";
						q_search2 += "and c_group_code = ? ";
					}
					
					if(search.containsKey("application_type") && search.get("application_type").length() > 0){
						q_search += "and c_type = ? ";
						q_search2 += "and c_type = ? ";
					}
					
					if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
						q_search += "and c_reseller_location = ? ";
						q_search2 += "and c_reseller_location = ? ";
					}
					
					if(search.containsKey("from_date") && search.get("from_date").length() > 0 && search.containsKey("to_date") && search.get("to_date").length() > 0){
						
						q_search += "and rcsd_date = to_date(?, 'DD/MM/YYYY') ";
						q_search2 += "and rcsd_date >= to_date(?, 'DD/MM/YYYY') "
									+ "and rcsd_date <= to_date(?, 'DD/MM/YYYY') ";
					}
					
					if(search.containsKey("status") && search.get("status").length() > 0){
						q_search += "and rcsd_status = ? ";
						q_search2 += "and rcsd_status = ? ";
					}
				}
				
				String sql = "select c_submit_date, rcsd_status_date, c_reference_no, c_type, c_created_date, rcsd_matrix, c_group_code, c_created_by, c_reseller_location, rcsd_status, "
						+ "(trunc(rcsd_status_date) - c_created_date) as duration_days, rcsd_sla, tblaccessrole.role_name, tbluser.name "
						+ "from rptcasestatusdaily "
						+ "left join casemast on rptcasestatusdaily.rcsd_case_id = casemast.c_id "
						+ "left join tblaccessrole on tblaccessrole.role_code = casemast.c_group_code "
						+ "left join tbluser on rptcasestatusdaily.rcsd_created_by = tbluser.login_id "
						+ "where rcsd_status in ("
						+ "	select distinct s_next from statusmast "
						+ "	where s_next not like 'CLOSE%') "
						+ q_search
						
						+ "union "
						
						+ "select c_submit_date, rcsd_status_date, c_reference_no, c_type, c_created_date, rcsd_matrix, c_group_code, c_created_by, c_reseller_location, rcsd_status, "
						+ "(trunc(rcsd_status_date) - c_created_date) as duration_days, rcsd_sla, tblaccessrole.role_name, tbluser.name "
						+ "from rptcasestatusdaily "
						+ "left join casemast on rptcasestatusdaily.rcsd_case_id = casemast.c_id "
						+ "left join tblaccessrole on tblaccessrole.role_code = casemast.c_group_code "
						+ "left join tbluser on rptcasestatusdaily.rcsd_created_by = tbluser.login_id "
						+ "where rcsd_status in ("
						+ "	select distinct s_next from statusmast "
						+ "	where s_next like 'CLOSE%') "
						+ q_search2;
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					//statement.setString(1, groupCode);
					
					int last_index = 0;
					/*for(String s : startArr) {
						statement.setString(++last_index, s);
					}*/
					
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("group_code"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("reseller_location"));
						}
						
						if(search.containsKey("from_date") && search.get("from_date").length() > 0 && search.containsKey("to_date") && search.get("to_date").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("to_date"));
						}
						
						if(search.containsKey("status") && search.get("status").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("status"));
						}
						
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("group_code"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("reseller_location"));
						}
						
						if(search.containsKey("from_date") && search.get("from_date").length() > 0 && search.containsKey("to_date") && search.get("to_date").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("from_date"));
							++last_index;
							statement.setString(last_index, search.get("to_date"));
						}
						
						if(search.containsKey("status") && search.get("status").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("status"));
						}
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							TreeMap<String, String> c = new TreeMap<String, String>();
							
							int duration = resultSet.getInt("duration_days");
							System.out.println("duration: " + duration);
							long created_date = ((Timestamp) resultSet.getTimestamp("c_created_date")).getTime();
							
							if(resultSet.getTimestamp("c_submit_date") != null){
								created_date = ((Timestamp) resultSet.getTimestamp("c_submit_date")).getTime();
							}
							
							String crtd_date = Casemast.formatCreatedDate("dd/MM/yyyy", created_date);
							//
							//long status_date = ((Timestamp) resultSet.getTimestamp("rcsd_status_date")).getTime();
							//String s_date = Casemast.formatCreatedDate("dd/MM/yyyy", status_date);
							//System.out.println("Start: " + s_date + ", End: " + crtd_date);
							//
							c.put("map_ref_no", resultSet.getString("c_reference_no"));
							c.put("application_type", resultSet.getString("c_type"));
							c.put("created_date", crtd_date);
							c.put("matrix", resultSet.getString("rcsd_matrix"));
							c.put("group_code", resultSet.getString("role_name") == null ? resultSet.getString("c_group_code") : resultSet.getString("role_name"));
							c.put("creator", StringUtils.isNotBlank(resultSet.getString("name"))? resultSet.getString("name"): resultSet.getString("c_created_by"));
							c.put("region", resultSet.getString("c_reseller_location"));
							c.put("status", resultSet.getString("rcsd_status"));
							c.put("duration", resultSet.getString("rcsd_sla"));
							//c.put("duration", Integer.toString(duration));
							cArr.add(c);
						}
						
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return cArr;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static ArrayList<TreeMap<String, String>> getDecisioningReportMonthly(TreeMap<String, String> search) {
		ArrayList<TreeMap<String, String>> cArr = new ArrayList<TreeMap<String, String>>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
				
				if(search != null) {
					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						q_search += "and c_group_code = ? ";
					}
					
					if(search.containsKey("application_type") && search.get("application_type").length() > 0){
						q_search += "and c_type = ? ";
					}
					
					if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
						q_search += "and c_reseller_location = ? ";
					}
					
					if(search.containsKey("year") && search.get("year").length() > 0){
						q_search += "and rcs_year = ? ";
					}
					
					if(search.containsKey("month") && search.get("month").length() > 0){
						q_search += "and rcs_month = ? ";
					}
				}
		
				String sql = "select rptcasestatus.*, trunc(rcs_status_date) as status_date, casemast.*, tblaccessrole.role_name "
							+ "from rptcasestatus "
							+ "left join casemast on rptcasestatus.rcs_case_id = casemast.c_id " 
							+ "left join tblaccessrole on role_code = c_group_code "
							+ "where rcs_action in "
							+ "('Approved. To Issue New Letter Of Acceptance', 'Approved. Request For MID Setup', 'Recommended', 'Declined', 'To Discuss. More Info / Doc Required') "
							+ q_search;
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					
					int last_index = 0;
					
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("group_code"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("reseller_location"));
						}
						
						if(search.containsKey("year") && search.get("year").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("year"));
						}
						
						if(search.containsKey("month") && search.get("month").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("month"));
						}
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							
							Date decision_pending_date = null;
							
							try{
								String sql2 = "select trunc(ch_created_date) as pending_date from casehist "
											+ "where ch_status = 'PENDING DECISION'"
											+ "ch_case_id = ?";
								
								PreparedStatement statement2 = conn.prepareStatement(sql2);
								
								
								try{
									statement2.setInt(1, resultSet.getInt("c_id"));
									
									ResultSet resultSet2 = statement2.executeQuery();
									
									try {
										while(resultSet2.next()){
											decision_pending_date = resultSet2.getDate("pending_date");
										}
									} catch(Exception e){
										e.printStackTrace();
									} finally {
										resultSet2.close();
									}
								} catch(Exception e){
									e.printStackTrace();
								} finally {
									statement2.close();
								}
							} catch (Exception e){
								Report.message = e.getMessage();
								return null;
							}
							
							TreeMap<String, String> c = new TreeMap<String, String>();
							
							long days = 0;
							if (decision_pending_date != null){
								days = daysBetween(decision_pending_date, resultSet.getDate("status_date"));
							}
							
							long created_date = ((Timestamp) resultSet.getTimestamp("c_created_date")).getTime();
							
							if(resultSet.getTimestamp("c_submit_date") != null){
								created_date = ((Timestamp) resultSet.getTimestamp("c_submit_date")).getTime();
							}
							
							String crtd_date = Casemast.formatCreatedDate("dd/MM/yyyy HH:mm:ss", created_date);
							
							c.put("map_ref_no", resultSet.getString("c_reference_no"));
							c.put("application_type", resultSet.getString("c_type"));
							c.put("created_date", crtd_date);
							c.put("matrix", resultSet.getString("rcs_matrix"));
							c.put("group_code", resultSet.getString("role_name") == null ? resultSet.getString("c_group_code") : resultSet.getString("role_name"));
							c.put("creator", resultSet.getString("c_created_by"));
							c.put("region", resultSet.getString("c_reseller_location"));
							c.put("status", resultSet.getString("rcs_status"));
							c.put("duration", Long.toString(days));
							
							cArr.add(c);
						}
						
					}  catch(Exception e){
						e.printStackTrace();
					}finally {
						resultSet.close();
					}
				} catch(Exception e){
					e.printStackTrace();
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return cArr;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static ArrayList<TreeMap<String, String>> getDecisioningReportDaily(TreeMap<String, String> search) {
		ArrayList<TreeMap<String, String>> cArr = new ArrayList<TreeMap<String, String>>();
		ArrayList<Date> cPublicHoliday = new ArrayList<Date>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
				
				if(search != null) {
					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						q_search += "and c_group_code = ? ";
					}
					
					if(search.containsKey("application_type") && search.get("application_type").length() > 0){
						q_search += "and c_type = ? ";
					}
					
					if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
						q_search += "and c_reseller_location = ? ";
					}
					
					if(search.containsKey("from_date") && search.get("from_date").length() > 0 && search.containsKey("to_date") && search.get("to_date").length() > 0){
						q_search += "and rcsd_date >= to_date(?, 'DD/MM/YYYY') "
								+ "and rcsd_date <= to_date(?, 'DD/MM/YYYY') ";
					}
				}
				
				String sql = "select rptcasestatusdaily.*, trunc(rcsd_status_date) as status_date, casemast.*, tblaccessrole.role_name "
							+ "from rptcasestatusdaily "
							+ "left join casemast on rptcasestatusdaily.rcsd_case_id = casemast.c_id "
							+ "left join tblaccessrole on role_code = c_group_code "
							+ "where rcsd_action in "
							+ "('Approved. To Issue New Letter Of Acceptance', 'Approved. Request For MID Setup', 'Recommended', 'Declined', 'To Discuss. More Info / Doc Required') "
							+ q_search;
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					
					int last_index = 0;
					
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("group_code"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("reseller_location"));
						}
						
						if(search.containsKey("from_date") && search.get("from_date").length() > 0 && search.containsKey("to_date") && search.get("to_date").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("from_date"));
							++last_index;
							statement.setString(last_index, search.get("to_date"));
						}
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try{
						//get public holiday
						String query = "select publicholiday.* from publicholiday ";
						PreparedStatement pStatement = conn.prepareStatement(query);
						try{
							ResultSet resultSet3 = pStatement.executeQuery();
						
							try {
								while(resultSet3.next()){
									Date phDate = resultSet3.getDate("PH_DATE");
									if(phDate != null){
										cPublicHoliday.add(phDate);
									}
								}
							} catch(Exception e) {
								e.printStackTrace();
							} finally {
								resultSet3.close();
							}
						} catch(Exception e) {
							e.printStackTrace();
						} finally {
							pStatement.close();
						}
					} catch (Exception e){
						Report.message = e.getMessage();
						return null;
					}
					
					
					try {
						while(resultSet.next()){
							
							Date decision_pending_date = null;
							String decision_action = "";
							
							try{
								String sql2 = "select ch_action, trunc(ch_created_date) as pending_date from casehist "
											+ "where ch_status = 'PENDING DECISION' and "
											+ "ch_case_id = ? order by ch_created_date desc";
								
								PreparedStatement statement2 = conn.prepareStatement(sql2);
								
								
								try{
									statement2.setInt(1, resultSet.getInt("c_id"));
									
									ResultSet resultSet2 = statement2.executeQuery();
									
									try {
										while(resultSet2.next()){
											decision_pending_date = resultSet2.getDate("pending_date");
											decision_action = resultSet2.getString("ch_action");
											System.out.println("Pending: " + decision_pending_date + ", action:" + decision_action);
											
											if("Task Note".equals(decision_action)){
												TreeMap<String, String> c = new TreeMap<String, String>();
												
												long days = 0;
												if (decision_pending_date != null){
													days = daysBetween(decision_pending_date, resultSet.getDate("status_date"), cPublicHoliday);
												}
												
												long created_date = ((Timestamp) resultSet.getTimestamp("c_created_date")).getTime();
												
												if(resultSet.getTimestamp("c_submit_date") != null){
													created_date = ((Timestamp) resultSet.getTimestamp("c_submit_date")).getTime();
												}
												
												String crtd_date = Casemast.formatCreatedDate("dd/MM/yyyy HH:mm:ss", created_date);
												
												c.put("map_ref_no", resultSet.getString("c_reference_no"));
												c.put("application_type", resultSet.getString("c_type"));
												c.put("created_date", crtd_date);
												c.put("matrix", resultSet.getString("rcsd_matrix"));
												c.put("group_code", resultSet.getString("role_name") == null ? resultSet.getString("c_group_code") : resultSet.getString("role_name"));
												c.put("creator", resultSet.getString("c_created_by"));
												c.put("region", resultSet.getString("c_reseller_location"));
												c.put("status", resultSet.getString("rcsd_status"));
												c.put("duration", Long.toString(days));
												
												cArr.add(c);
											}
										}
									} catch(Exception e) {
										e.printStackTrace();
									} finally {
										resultSet2.close();
									}
								} catch(Exception e) {
									e.printStackTrace();
								} finally {
									statement2.close();
								}
							} catch (Exception e){
								Report.message = e.getMessage();
								return null;
							}
							
							TreeMap<String, String> c = new TreeMap<String, String>();
							
							long days = 0;
							if (decision_pending_date != null &&  decision_pending_date.compareTo(resultSet.getDate("status_date")) < 0){
								days = daysBetween(decision_pending_date, resultSet.getDate("status_date"), cPublicHoliday);
							
								long created_date = ((Timestamp) resultSet.getTimestamp("c_created_date")).getTime();
							
								if(resultSet.getTimestamp("c_submit_date") != null){
								created_date = ((Timestamp) resultSet.getTimestamp("c_submit_date")).getTime();
								}
							
								String crtd_date = Casemast.formatCreatedDate("dd/MM/yyyy HH:mm:ss", created_date);
							
								c.put("map_ref_no", resultSet.getString("c_reference_no"));
								c.put("application_type", resultSet.getString("c_type"));
								c.put("created_date", crtd_date);
								c.put("matrix", resultSet.getString("rcsd_matrix"));
								c.put("group_code", resultSet.getString("role_name") == null ? resultSet.getString("c_group_code") : resultSet.getString("role_name"));
								c.put("creator", resultSet.getString("c_created_by"));
								c.put("region", resultSet.getString("c_reseller_location"));
								c.put("status", resultSet.getString("rcsd_status"));
								c.put("duration", Long.toString(days));
							
								cArr.add(c);
							}
						}
						
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return cArr;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static ArrayList<TreeMap<String, String>> getSummaryReportMonthly(TreeMap<String, String> search) {
		ArrayList<TreeMap<String, String>> cArr = new ArrayList<TreeMap<String, String>>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
				String sql_choice = "";
				
				String sql1 = "select casemast.c_group_code, "
							+ "sum(case when casemast.c_type = ? and rcs_matrix = ? then 1 else 0 end) as total, "
							+ "sum(case when casemast.c_type = ? and rcs_matrix = ? and rcs_action = 'Approved. To Issue New Letter of Acceptance.' then 1 else 0 end) col_a, "
							+ "sum(case when casemast.c_type = ? and rcs_matrix = ? and rcs_action = 'Approved. Request for Cardlink Update' then 1 else 0 end) as col_b, "
							+ "sum(case when casemast.c_type = ? and rcs_matrix = ? and rcs_action = 'Recommended' then 1 else 0 end) as col_c, "
							+ "sum(case when casemast.c_type = ? and rcs_matrix = ? and rcs_action = 'Decline' then 1 else 0 end) as col_d, "
							+ "sum(case when casemast.c_type = ? and rcs_matrix = ? and rcs_action = 'Close - Cancel' then 1 else 0 end) as col_e, "
							+ "sum(case when casemast.c_type = ? and rcs_matrix = ? and rcs_action = 'Close - Withdraw' then 1 else 0 end) as col_f, "
							+ "sum(case when casemast.c_type = ? and rcs_matrix = ? and rcs_action = 'Close - Reject' then 1 else 0 end) as col_g, "
							+ "sum(case when casemast.c_type = ? and rcs_matrix = ? and rcs_action = 'Reject - Data Discrepancy' then 1 else 0 end) as col_h, "
							+ "sum(case when casemast.c_type = ? and rcs_matrix = ? and rcs_action = 'Reject - Doc Discrepancy' then 1 else 0 end) as col_i, "
							+ "sum(case when casemast.c_type = ? and rcs_matrix = ? and rcs_action = 'Reject - Data & Doc Discrepancy' then 1 else 0 end) as col_j, "
							+ "sum(case when casemast.c_type = ? and rcs_matrix = ? and rcs_action = 'Close - Installation & Training Completed' then 1 else 0 end) as col_k "
							+ "from rptcasestatus "
							+ "left join casemast on rptcasestatus.rcs_case_id = casemast.c_id "
							+ "where 1 = 1 ";
				
				String sql2 = "select casemast.c_group_code, "
							+ "sum(case when casemast.c_type = ? then 1 else 0 end) as total, "
							+ "sum(case when casemast.c_type = ? and rcs_action = 'Approved. To Issue New Letter of Acceptance.' then 1 else 0 end) col_a, "
							+ "sum(case when casemast.c_type = ? and rcs_action = 'Approved. Request for Cardlink Update' then 1 else 0 end) as col_b, "
							+ "sum(case when casemast.c_type = ? and rcs_action = 'Recommended' then 1 else 0 end) as col_c, "
							+ "sum(case when casemast.c_type = ? and rcs_action = 'Decline' then 1 else 0 end) as col_d, "
							+ "sum(case when casemast.c_type = ? and rcs_action = 'Close - Cancel' then 1 else 0 end) as col_e, "
							+ "sum(case when casemast.c_type = ? and rcs_action = 'Close - Withdraw' then 1 else 0 end) as col_f, "
							+ "sum(case when casemast.c_type = ? and rcs_action = 'Close - Reject' then 1 else 0 end) as col_g, "
							+ "sum(case when casemast.c_type = ? and rcs_action = 'Reject - Data Discrepancy' then 1 else 0 end) as col_h, "
							+ "sum(case when casemast.c_type = ? and rcs_action = 'Reject - Doc Discrepancy' then 1 else 0 end) as col_i, "
							+ "sum(case when casemast.c_type = ? and rcs_action = 'Reject - Data & Doc Discrepancy' then 1 else 0 end) as col_j, "
							+ "sum(case when casemast.c_type = ? and rcs_action = 'Close - Installation & Training Completed' then 1 else 0 end) as col_k "
							+ "from rptcasestatus "
							+ "left join casemast on rptcasestatus.rcs_case_id = casemast.c_id "
							+ "where 1 = 1 ";
				
				String sql3 = "select casemast.c_group_code, "
							+ "count(casemast.c_id) as total, "
							+ "sum(case when rcs_action = 'Approved. To Issue New Letter of Acceptance.' then 1 else 0 end) col_a, "
							+ "sum(case when rcs_action = 'Approved. Request for Cardlink Update' then 1 else 0 end) as col_b, "
							+ "sum(case when rcs_action = 'Recommended' then 1 else 0 end) as col_c, "
							+ "sum(case when rcs_action = 'Decline' then 1 else 0 end) as col_d, "
							+ "sum(case when rcs_action = 'Close - Cancel' then 1 else 0 end) as col_e, "
							+ "sum(case when rcs_action = 'Close - Withdraw' then 1 else 0 end) as col_f, "
							+ "sum(case when rcs_action = 'Close - Reject' then 1 else 0 end) as col_g, "
							+ "sum(case when rcs_action = 'Reject - Data Discrepancy' then 1 else 0 end) as col_h, "
							+ "sum(case when rcs_action = 'Reject - Doc Discrepancy' then 1 else 0 end) as col_i, "
							+ "sum(case when rcs_action = 'Reject - Data & Doc Discrepancy' then 1 else 0 end) as col_j, "
							+ "sum(case when rcs_action = 'Close - Installation & Training Completed' then 1 else 0 end) as col_k "
							+ "from rptcasestatus "
							+ "left join casemast on rptcasestatus.rcs_case_id = casemast.c_id "
							+ "where 1 = 1 ";
				
				if(search != null) {
					
					if(search.containsKey("year") && search.get("year").length() > 0){
						q_search += "and rcs_year = ? ";
					}
					
					if(search.containsKey("month") && search.get("month").length() > 0){
						q_search += "and rcs_month = ? ";
					}
					
					if(search.containsKey("application_type") && search.get("application_type").length() > 0){
						if (search.get("application_type").equals("New Application")){
							//New Application
							sql_choice += sql1;
						} else {
							//Extend Facility, e-Commerce, Additional Terminal
							sql_choice += sql2;
						}
					} else {
						//Non selected
						sql_choice += sql3;
					}
				}
				
				String sql = sql_choice
							+ q_search;
				
				sql	+= " group by casemast.c_group_code";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					
					int last_index = 0;
					
					if(search != null) {
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							if (search.get("application_type").equals("New Application")){
								//New Application
								if (search.containsKey("matrix") && search.get("matrix").length() > 0){
									for (int i = 1; i <= 12; i++){
										++last_index;
										statement.setString(last_index, search.get("application_type"));
										++last_index;
										statement.setString(last_index, search.get("matrix"));
									}
								} else {
									for (int i = 1; i <= 12; i++){
										++last_index;
										statement.setString(last_index, search.get("application_type"));
										++last_index;
										statement.setString(last_index, null);
									}
								}
							} else {
								//Extend Facility, e-Commerce, Additional Terminal
								for (int i = 1; i <= 12; i++){
									++last_index;
									statement.setString(last_index, search.get("application_type"));
								}
							}
						}
						
						if(search.containsKey("year") && search.get("year").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("year"));
						}
						
						if(search.containsKey("month") && search.get("month").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("month"));
						}
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							
							TreeMap<String, String> c = new TreeMap<String, String>();
							
							int total = resultSet.getInt("col_a") + resultSet.getInt("col_b") + resultSet.getInt("col_c") + resultSet.getInt("col_d") + resultSet.getInt("col_e") + resultSet.getInt("col_f") + resultSet.getInt("col_g") + resultSet.getInt("col_h") + resultSet.getInt("col_i") + resultSet.getInt("col_j") + resultSet.getInt("col_k");
							
							c.put("group", resultSet.getString("c_group_code"));
							c.put("total_submission", Integer.toString( resultSet.getInt("total")));
							c.put("col_a", Integer.toString( resultSet.getInt("col_a")));
							c.put("col_b", Integer.toString( resultSet.getInt("col_b")));
							c.put("col_c", Integer.toString( resultSet.getInt("col_c")));
							c.put("col_d", Integer.toString( resultSet.getInt("col_d")));
							c.put("col_e", Integer.toString( resultSet.getInt("col_e")));
							c.put("col_f", Integer.toString( resultSet.getInt("col_f")));
							c.put("col_g", Integer.toString( resultSet.getInt("col_g")));
							c.put("col_h", Integer.toString( resultSet.getInt("col_h")));
							c.put("col_i", Integer.toString( resultSet.getInt("col_i")));
							c.put("col_j", Integer.toString( resultSet.getInt("col_j")));
							c.put("col_k", Integer.toString( resultSet.getInt("col_k")));
							c.put("total", Integer.toString( total ));
							
							cArr.add(c);
						}
						
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return cArr;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static ArrayList<TreeMap<String, String>> getSummaryReportDaily(TreeMap<String, String> search) {
		ArrayList<TreeMap<String, String>> cArr = new ArrayList<TreeMap<String, String>>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search1 = "";
				String q_search2 = "";
				String sql_choice1 = "";
				String sql_choice2 = "";
				
				String sql11 = "select casemast.c_group_code, "
							+ "sum(case when casemast.c_type = ? and rcsd_matrix = ? then 1 else 0 end) as total, "
							+ "sum(case when casemast.c_type = ? and rcsd_matrix = ? and rcsd_action = 'Approved. To Issue New Letter Of Acceptance.' then 1 else 0 end) as col_a, "
							+ "sum(case when casemast.c_type = ? and rcsd_matrix = ? and rcsd_action = 'Approved. Request For MID Setup' then 1 else 0 end) as col_b, "
							+ "sum(case when casemast.c_type = ? and rcsd_matrix = ? and rcsd_action = 'Recommended' then 1 else 0 end) as col_c, "
							+ "sum(case when casemast.c_type = ? and rcsd_matrix = ? and rcsd_action = 'Declined' then 1 else 0 end) as col_d, "
							+ "sum(case when casemast.c_type = ? and rcsd_matrix = ? and rcsd_action = 'To Discuss. More Info / Doc Required' then 1 else 0 end) as col_d1, "
							+ "sum(case when casemast.c_type = ? and rcsd_matrix = ? and rcsd_action = 'Close - Cancel' then 1 else 0 end) as col_e, "
							+ "sum(case when casemast.c_type = ? and rcsd_matrix = ? and rcsd_action = 'Close - Withdraw' then 1 else 0 end) as col_f, "
							+ "sum(case when casemast.c_type = ? and rcsd_matrix = ? and rcsd_action = 'Close - Reject' then 1 else 0 end) as col_g, "
							+ "sum(case when casemast.c_type = ? and rcsd_matrix = ? and rcsd_action = 'Reject - Data Discrepancy' then 1 else 0 end) as col_h, "
							+ "sum(case when casemast.c_type = ? and rcsd_matrix = ? and rcsd_action = 'Reject - Doc Discrepancy' then 1 else 0 end) as col_i, "
							+ "sum(case when casemast.c_type = ? and rcsd_matrix = ? and rcsd_action = 'Reject - Data & Doc Discrepancy' then 1 else 0 end) as col_j, "
							+ "sum(case when casemast.c_type = ? and rcsd_matrix = ? and rcsd_action = 'Close - Installation & Training Completed' then 1 else 0 end) as col_k "
							+ "from rptcasestatusdaily "
							+ "left join casemast on rptcasestatusdaily.rcsd_case_id = casemast.c_id "
							+ "where rcsd_status in ("
							+ "	select distinct s_next from statusmast "
							+ "	where s_next not like 'CLOSE%') ";
							
				String sql12 = "union "
							
							+ "select casemast.c_group_code, "
							+ "sum(case when casemast.c_type = ? and rcsd_matrix = ? then 1 else 0 end) as total, "
							+ "sum(case when casemast.c_type = ? and rcsd_matrix = ? and rcsd_action = 'Approved. To Issue New Letter Of Acceptance.' then 1 else 0 end) as col_a, "
							+ "sum(case when casemast.c_type = ? and rcsd_matrix = ? and rcsd_action = 'Approved. Request For MID Setup' then 1 else 0 end) as col_b, "
							+ "sum(case when casemast.c_type = ? and rcsd_matrix = ? and rcsd_action = 'Recommended' then 1 else 0 end) as col_c, "
							+ "sum(case when casemast.c_type = ? and rcsd_matrix = ? and rcsd_action = 'Declined' then 1 else 0 end) as col_d, "
							+ "sum(case when casemast.c_type = ? and rcsd_matrix = ? and rcsd_action = 'To Discuss. More Info / Doc Required' then 1 else 0 end) as col_d1, "
							+ "sum(case when casemast.c_type = ? and rcsd_matrix = ? and rcsd_action = 'Close - Cancel' then 1 else 0 end) as col_e, "
							+ "sum(case when casemast.c_type = ? and rcsd_matrix = ? and rcsd_action = 'Close - Withdraw' then 1 else 0 end) as col_f, "
							+ "sum(case when casemast.c_type = ? and rcsd_matrix = ? and rcsd_action = 'Close - Reject' then 1 else 0 end) as col_g, "
							+ "sum(case when casemast.c_type = ? and rcsd_matrix = ? and rcsd_action = 'Reject - Data Discrepancy' then 1 else 0 end) as col_h, "
							+ "sum(case when casemast.c_type = ? and rcsd_matrix = ? and rcsd_action = 'Reject - Doc Discrepancy' then 1 else 0 end) as col_i, "
							+ "sum(case when casemast.c_type = ? and rcsd_matrix = ? and rcsd_action = 'Reject - Data & Doc Discrepancy' then 1 else 0 end) as col_j, "
							+ "sum(case when casemast.c_type = ? and rcsd_matrix = ? and rcsd_action = 'Close - Installation & Training Completed' then 1 else 0 end) as col_k "
							+ "from rptcasestatusdaily "
							+ "left join casemast on rptcasestatusdaily.rcsd_case_id = casemast.c_id "
							+ "where rcsd_status in ("
							+ "	select distinct s_next from statusmast"
							+ "	where s_next like 'CLOSE%') ";
				
				String sql21 = "select casemast.c_group_code, "
							+ "sum(case when casemast.c_type = ? then 1 else 0 end) as total, "
							+ "sum(case when casemast.c_type = ? and rcsd_action = 'Approved. To Issue New Letter Of Acceptance.' then 1 else 0 end) as col_a, "
							+ "sum(case when casemast.c_type = ? and rcsd_action = 'Approved. Request For MID Setup' then 1 else 0 end) as col_b, "
							+ "sum(case when casemast.c_type = ? and rcsd_action = 'Recommended' then 1 else 0 end) as col_c, "
							+ "sum(case when casemast.c_type = ? and rcsd_action = 'Declined' then 1 else 0 end) as col_d, "
							+ "sum(case when casemast.c_type = ? and rcsd_action = 'To Discuss. More Info / Doc Required' then 1 else 0 end) as col_d1, "
							+ "sum(case when casemast.c_type = ? and rcsd_action = 'Close - Cancel' then 1 else 0 end) as col_e, "
							+ "sum(case when casemast.c_type = ? and rcsd_action = 'Close - Withdraw' then 1 else 0 end) as col_f, "
							+ "sum(case when casemast.c_type = ? and rcsd_action = 'Close - Reject' then 1 else 0 end) as col_g, "
							+ "sum(case when casemast.c_type = ? and rcsd_action = 'Reject - Data Discrepancy' then 1 else 0 end) as col_h, "
							+ "sum(case when casemast.c_type = ? and rcsd_action = 'Reject - Doc Discrepancy' then 1 else 0 end) as col_i, "
							+ "sum(case when casemast.c_type = ? and rcsd_action = 'Reject - Data & Doc Discrepancy' then 1 else 0 end) as col_j, "
							+ "sum(case when casemast.c_type = ? and rcsd_action = 'Close - Installation & Training Completed' then 1 else 0 end) as col_k "
							+ "from rptcasestatusdaily "
							+ "left join casemast on rptcasestatusdaily.rcsd_case_id = casemast.c_id "
							+ "where rcsd_status in ("
							+ "	select distinct s_next from statusmast "
							+ "	where s_next not like 'CLOSE%') ";
							
				String sql22 = "union "
							
							+ "select casemast.c_group_code, "
							+ "sum(case when casemast.c_type = ? then 1 else 0 end) as total, "
							+ "sum(case when casemast.c_type = ? and rcsd_action = 'Approved. To Issue New Letter Of Acceptance.' then 1 else 0 end) as col_a, "
							+ "sum(case when casemast.c_type = ? and rcsd_action = 'Approved. Request For MID Setup' then 1 else 0 end) as col_b, "
							+ "sum(case when casemast.c_type = ? and rcsd_action = 'Recommended' then 1 else 0 end) as col_c, "
							+ "sum(case when casemast.c_type = ? and rcsd_action = 'Declined' then 1 else 0 end) as col_d, "
							+ "sum(case when casemast.c_type = ? and rcsd_action = 'To Discuss. More Info / Doc Required' then 1 else 0 end) as col_d1, "
							+ "sum(case when casemast.c_type = ? and rcsd_action = 'Close - Cancel' then 1 else 0 end) as col_e, "
							+ "sum(case when casemast.c_type = ? and rcsd_action = 'Close - Withdraw' then 1 else 0 end) as col_f, "
							+ "sum(case when casemast.c_type = ? and rcsd_action = 'Close - Reject' then 1 else 0 end) as col_g, "
							+ "sum(case when casemast.c_type = ? and rcsd_action = 'Reject - Data Discrepancy' then 1 else 0 end) as col_h, "
							+ "sum(case when casemast.c_type = ? and rcsd_action = 'Reject - Doc Discrepancy' then 1 else 0 end) as col_i, "
							+ "sum(case when casemast.c_type = ? and rcsd_action = 'Reject - Data & Doc Discrepancy' then 1 else 0 end) as col_j, "
							+ "sum(case when casemast.c_type = ? and rcsd_action = 'Close - Installation & Training Completed' then 1 else 0 end) as col_k "
							+ "from rptcasestatusdaily "
							+ "left join casemast on rptcasestatusdaily.rcsd_case_id = casemast.c_id "
							+ "where rcsd_status in ("
							+ "	select distinct s_next from statusmast"
							+ "	where s_next like 'CLOSE%') ";
				
				String sql31 = "select casemast.c_group_code, "
							+ "count(casemast.c_id) as total, "
							+ "sum(case when rcsd_action = 'Approved. To Issue New Letter Of Acceptance.' then 1 else 0 end) as col_a, "
							+ "sum(case when rcsd_action = 'Approved. Request For MID Setup' then 1 else 0 end) as col_b, "
							+ "sum(case when rcsd_action = 'Recommended' then 1 else 0 end) as col_c, "
							+ "sum(case when rcsd_action = 'Declined' then 1 else 0 end) as col_d, "
							+ "sum(case when rcsd_action = 'To Discuss. More Info / Doc Required' then 1 else 0 end) as col_d1, "
							+ "sum(case when rcsd_action = 'Close - Cancel' then 1 else 0 end) as col_e, "
							+ "sum(case when rcsd_action = 'Close - Withdraw' then 1 else 0 end) as col_f, "
							+ "sum(case when rcsd_action = 'Close - Reject' then 1 else 0 end) as col_g, "
							+ "sum(case when rcsd_action = 'Reject - Data Discrepancy' then 1 else 0 end) as col_h, "
							+ "sum(case when rcsd_action = 'Reject - Doc Discrepancy' then 1 else 0 end) as col_i, "
							+ "sum(case when rcsd_action = 'Reject - Data & Doc Discrepancy' then 1 else 0 end) as col_j, "
							+ "sum(case when rcsd_action = 'Close - Installation & Training Completed' then 1 else 0 end) as col_k "
							+ "from rptcasestatusdaily "
							+ "left join casemast on rptcasestatusdaily.rcsd_case_id = casemast.c_id "
							+ "where rcsd_status in ("
							+ "	select distinct s_next from statusmast "
							+ "	where s_next not like 'CLOSE%') ";
							
				String sql32 = "union "
							
							+ "select casemast.c_group_code, "
							+ "count(casemast.c_id) as total, "
							+ "sum(case when rcsd_action = 'Approved. To Issue New Letter Of Acceptance.' then 1 else 0 end) as col_a, "
							+ "sum(case when rcsd_action = 'Approved. Request For MID Setup' then 1 else 0 end) as col_b, "
							+ "sum(case when rcsd_action = 'Recommended' then 1 else 0 end) as col_c, "
							+ "sum(case when rcsd_action = 'Declined' then 1 else 0 end) as col_d, "
							+ "sum(case when rcsd_action = 'To Discuss. More Info / Doc Required' then 1 else 0 end) as col_d1, "
							+ "sum(case when rcsd_action = 'Close - Cancel' then 1 else 0 end) as col_e, "
							+ "sum(case when rcsd_action = 'Close - Withdraw' then 1 else 0 end) as col_f, "
							+ "sum(case when rcsd_action = 'Close - Reject' then 1 else 0 end) as col_g, "
							+ "sum(case when rcsd_action = 'Reject - Data Discrepancy' then 1 else 0 end) as col_h, "
							+ "sum(case when rcsd_action = 'Reject - Doc Discrepancy' then 1 else 0 end) as col_i, "
							+ "sum(case when rcsd_action = 'Reject - Data & Doc Discrepancy' then 1 else 0 end) as col_j, "
							+ "sum(case when rcsd_action = 'Close - Installation & Training Completed' then 1 else 0 end) as col_k "
							+ "from rptcasestatusdaily "
							+ "left join casemast on rptcasestatusdaily.rcsd_case_id = casemast.c_id "
							+ "where rcsd_status in ("
							+ "	select distinct s_next from statusmast"
							+ "	where s_next like 'CLOSE%') ";
				
				if(search != null) {
					
					if(search.containsKey("from_date") && search.get("from_date").length() > 0 && search.containsKey("to_date") && search.get("to_date").length() > 0){
						q_search1 += "and rcsd_date = to_date(?, 'DD/MM/YYYY') ";
						q_search2 += "and rcsd_date >= to_date(?, 'DD/MM/YYYY') "
									+ "and rcsd_date <= to_date(?, 'DD/MM/YYYY') ";
					}
					
					if(search.containsKey("application_type") && search.get("application_type").length() > 0){
						if (search.get("application_type").equals("New Application")){
							//New Application
							sql_choice1 += sql11;
							sql_choice2 += sql12;
						} else {
							//Extend Facility, e-Commerce, Add Terminal 
							sql_choice1 += sql21;
							sql_choice2 += sql22;
						}
					} else {
						//Non selected
						sql_choice1 += sql31;
						sql_choice2 += sql32;
					}
				}
				
				String sql = sql_choice1
							+ q_search1
							+ " group by casemast.c_group_code "
							+ sql_choice2
							+ q_search2
							+ " group by casemast.c_group_code";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					
					int last_index = 0;
					
					if(search != null) {
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							if (search.get("application_type").equals("New Application")){
								//New Application
								if (search.containsKey("matrix") && search.get("matrix").length() > 0){
									for (int i = 1; i <= 12; i++){
										++last_index;
										statement.setString(last_index, search.get("application_type"));
										++last_index;
										statement.setString(last_index, search.get("matrix"));
									}
									if (search.containsKey("to_date") && search.get("to_date").length() > 0){
										++last_index;
										statement.setString(last_index, search.get("to_date"));
									}
									for (int i = 1; i <= 12; i++){
										++last_index;
										statement.setString(last_index, search.get("application_type"));
										++last_index;
										statement.setString(last_index, search.get("matrix"));
									}
									if (search.containsKey("from_date") && search.get("from_date").length() > 0 && search.containsKey("to_date") && search.get("to_date").length() > 0){
										++last_index;
										statement.setString(last_index, search.get("from_date"));
										++last_index;
										statement.setString(last_index, search.get("to_date"));
									}
								} else {
									for (int i = 1; i <= 12; i++){
										++last_index;
										statement.setString(last_index, search.get("application_type"));
										++last_index;
										statement.setString(last_index, null);
									}
									if (search.containsKey("to_date") && search.get("to_date").length() > 0){
										++last_index;
										statement.setString(last_index, search.get("to_date"));
									}
									for (int i = 1; i <= 12; i++){
										++last_index;
										statement.setString(last_index, search.get("application_type"));
										++last_index;
										statement.setString(last_index, null);
									}
									if (search.containsKey("from_date") && search.get("from_date").length() > 0 && search.containsKey("to_date") && search.get("to_date").length() > 0){
										++last_index;
										statement.setString(last_index, search.get("from_date"));
										++last_index;
										statement.setString(last_index, search.get("to_date"));
									}
								}
							} else {
								//Extend Facility, e-Commerce, Additional Terminal
								for (int i = 1; i <= 12; i++){
									++last_index;
									statement.setString(last_index, search.get("application_type"));
								}
								if (search.containsKey("to_date") && search.get("to_date").length() > 0){
									++last_index;
									statement.setString(last_index, search.get("to_date"));
								}
								for (int i = 1; i <= 12; i++){
									++last_index;
									statement.setString(last_index, search.get("application_type"));
								}
								if (search.containsKey("from_date") && search.get("from_date").length() > 0 && search.containsKey("to_date") && search.get("to_date").length() > 0){
									++last_index;
									statement.setString(last_index, search.get("from_date"));
									++last_index;
									statement.setString(last_index, search.get("to_date"));
								}
							}
						} else {
							//Non selected
							if (search.containsKey("to_date") && search.get("to_date").length() > 0){
								++last_index;
								statement.setString(last_index, search.get("to_date"));
							}
		
							if (search.containsKey("from_date") && search.get("from_date").length() > 0 && search.containsKey("to_date") && search.get("to_date").length() > 0){
								++last_index;
								statement.setString(last_index, search.get("from_date"));
								++last_index;
								statement.setString(last_index, search.get("to_date"));
							}
						}
						
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							
							TreeMap<String, String> c = new TreeMap<String, String>();
							
							int total = resultSet.getInt("col_a") + resultSet.getInt("col_b") + resultSet.getInt("col_c") + resultSet.getInt("col_d") + resultSet.getInt("col_e") + resultSet.getInt("col_f") + resultSet.getInt("col_g") + resultSet.getInt("col_h") + resultSet.getInt("col_i") + resultSet.getInt("col_j") + resultSet.getInt("col_k");
							
							c.put("group", resultSet.getString("c_group_code"));
							c.put("total_submission", Integer.toString( resultSet.getInt("total")));
							c.put("col_a", Integer.toString( resultSet.getInt("col_a")));
							c.put("col_b", Integer.toString( resultSet.getInt("col_b")));
							c.put("col_c", Integer.toString( resultSet.getInt("col_c")));
							c.put("col_d", Integer.toString( resultSet.getInt("col_d")));
							c.put("col_d1", Integer.toString( resultSet.getInt("col_d1")));
							c.put("col_e", Integer.toString( resultSet.getInt("col_e")));
							c.put("col_f", Integer.toString( resultSet.getInt("col_f")));
							c.put("col_g", Integer.toString( resultSet.getInt("col_g")));
							c.put("col_h", Integer.toString( resultSet.getInt("col_h")));
							c.put("col_i", Integer.toString( resultSet.getInt("col_i")));
							c.put("col_j", Integer.toString( resultSet.getInt("col_j")));
							c.put("col_k", Integer.toString( resultSet.getInt("col_k")));
							c.put("total", Integer.toString( total ));
							
							cArr.add(c);
						}
						
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return cArr;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static ArrayList<TreeMap<String, String>> getProcessingProductivityReportMonthly(TreeMap<String, String> search) {
		ArrayList<TreeMap<String, String>> cArr = new ArrayList<TreeMap<String, String>>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
				
				if(search != null) {
					if(search.containsKey("year") && search.get("year").length() > 0){
						q_search += "and rcs_year = ? ";
					}
					
					if(search.containsKey("month") && search.get("month").length() > 0){
						q_search += "and rcs_month = ? ";
					}
				}
				
				/*String sql = "select rcs_created_by, "
							+ "	count(rcs_case_id) as total,"
							+ "	sum(case when rcs_action = 'Credit Check Result Attached' then 1 else 0 end) as col_a, "
							+ "	sum(case when rcs_action = 'Credit Check Complete' then 1 else 0 end) as col_b, "
							+ " sum(case when rcs_action = 'Preupdate Validation Done' then 1 else 0 end) as col_c, "
							+ "	sum(case when rcs_action = 'Request Update Confirmation' then 1 else 0 end) as col_d, "
							+ "	sum(case when rcs_action = 'Update Done. Request Verification' then 1 else 0 end) as col_e, "
							+ "	sum(case when rcs_action = 'Incomplete Doc.,But Proceed To Update' then 1 else 0 end) as col_f, "
							+ "	sum(case when rcs_action = 'Verified. Barcode Done. Pending Doc' then 1 else 0 end) as col_g "
							+ "from rptcasestatus "
							+ "where 1 = 1 "
							+ q_search
							+ "group by rcs_created_by ";*/
				StringBuffer strQuery = new StringBuffer();
				strQuery.append("select rcs_created_by, count(rcs_case_id) as total, name, role_name, ");
				for(int i = 0; i < reportAction.length; i++){
					if(i == reportAction.length - 1){
						strQuery.append(" sum(case when rcs_action = '" + reportAction[i] + "' then 1 else 0 end) as col_" + (i+1));
					}else{
						strQuery.append(" sum(case when rcs_action = '" + reportAction[i] + "' then 1 else 0 end) as col_" +( i+1) +", ");
					}
				}			
				strQuery.append(" from rptcasestatus");
				strQuery.append(" left join tbluser on (rptcasestatus.rcs_created_by = tbluser.login_id) ");
				strQuery.append(" inner join tblaccessrole on (tbluser.role_code = tblaccessrole.role_code) ");
				strQuery.append(" where 1 = 1 ");
				strQuery.append(q_search);
				strQuery.append("group by rcs_created_by, name");
				
				//PreparedStatement statement = conn.prepareStatement(sql);
				PreparedStatement statement = conn.prepareStatement(strQuery.toString());
				
				try {
					
					int last_index = 0;
					
					if(search != null) {
						
						if(search.containsKey("year") && search.get("year").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("year"));
						}
						
						if(search.containsKey("month") && search.get("month").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("month"));
						}
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							
							TreeMap<String, String> c = new TreeMap<String, String>();
							
							//int total_work = resultSet.getInt("col_a") + resultSet.getInt("col_b") + resultSet.getInt("col_c") + resultSet.getInt("col_d") + resultSet.getInt("col_e") + resultSet.getInt("col_f") + resultSet.getInt("col_g");
							int total_work = 0;
							for(int i = 0; i < reportAction.length; i++){
								total_work += resultSet.getInt("col_" + (i + 1));
							}
							c.put("creator", resultSet.getString("rcs_created_by"));
							c.put("creator_name", resultSet.getString("name"));
							c.put("total_applications", resultSet.getString("total"));
							c.put("role_name",  resultSet.getString("role_name"));
							
							/*c.put("col_a", resultSet.getString("col_a"));
							c.put("col_b", resultSet.getString("col_b"));
							c.put("col_c", resultSet.getString("col_c"));
							c.put("col_d", resultSet.getString("col_d"));
							c.put("col_e", resultSet.getString("col_e"));
							c.put("col_f", resultSet.getString("col_f"));
							c.put("col_g", resultSet.getString("col_g"));*/
							
							for(int i = 0; i < reportAction.length; i++){
								c.put("col_" + (i + 1), resultSet.getString("col_" + (i + 1)));
							}
							c.put("total_work", Integer.toString(total_work));
							
							cArr.add(c);
						}
						
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return cArr;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static ArrayList<TreeMap<String, String>> getProcessingProductivityReportDaily(TreeMap<String, String> search) {
		ArrayList<TreeMap<String, String>> cArr = new ArrayList<TreeMap<String, String>>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search1 = "";
				String q_search2 = "";
				
				if(search != null) {
					if(search.containsKey("from_date") && search.get("from_date").length() > 0 && search.containsKey("to_date") && search.get("to_date").length() > 0){
						q_search1 += "and rcsd_date = to_date(?, 'DD/MM/YYYY') ";
						q_search2 += "and rcsd_date >= to_date(?, 'DD/MM/YYYY') "
									+ "and rcsd_date <= to_date(?, 'DD/MM/YYYY') ";
					}
				}
				
				/*String sql = "select rcsd_created_by, "
							+ "	count(rcsd_case_id) as total,"
							+ "	sum(case when rcsd_action = 'Credit Check Result attached' then 1 else 0 end) as col_a, "
							+ "	sum(case when rcsd_action = 'Credit Check Complete' then 1 else 0 end) as col_b, "
							+ " sum(case when rcsd_action = 'Preupdate Validation Done' then 1 else 0 end) as col_c, "
							+ "	sum(case when rcsd_action = 'Request Update Confirmation' then 1 else 0 end) as col_d, "
							+ "	sum(case when rcsd_action = 'Update Done. Request Verification' then 1 else 0 end) as col_e, "
							+ "	sum(case when rcsd_action = 'Incomplete Doc.,But Proceed To Update' then 1 else 0 end) as col_f, "
							+ "	sum(case when rcsd_action = 'Verified.  Barcode Done. Pending Doc' then 1 else 0 end) as col_g "
							+ "from rptcasestatusdaily "
							+ "where rcsd_status in ("
							+ " select distinct s_next from statusmast"
							+ "	where s_next not like 'CLOSE%') "
							+ q_search1
							+ "group by rcsd_created_by "
							
							+ "union "
						
							+ "select rcsd_created_by, "
							+ "	count(rcsd_case_id) as total,"
							+ "	sum(case when rcsd_action = 'Credit Check Result attached' then 1 else 0 end) as col_a, "
							+ "	sum(case when rcsd_action = 'Credit Check Complete' then 1 else 0 end) as col_b, "
							+ " sum(case when rcsd_action = 'Preupdate Validation Done' then 1 else 0 end) as col_c, "
							+ "	sum(case when rcsd_action = 'Request Update Confirmation' then 1 else 0 end) as col_d, "
							+ "	sum(case when rcsd_action = 'Update Done. Request Verification' then 1 else 0 end) as col_e, "
							+ "	sum(case when rcsd_action = 'Incomplete Doc.,But Proceed To Update' then 1 else 0 end) as col_f, "
							+ "	sum(case when rcsd_action = 'Verified.  Barcode Done. Pending Doc' then 1 else 0 end) as col_g "
							+ "from rptcasestatusdaily "
							+ "where rcsd_status in ("
							+ " select distinct s_next from statusmast"
							+ "	where s_next like 'CLOSE%') "
							+ q_search2
							+ "group by rcsd_created_by ";*/
				
				StringBuffer strQuery = new StringBuffer();
				strQuery.append("select rcsd_created_by, count(rcsd_case_id) as total, name, role_name, ");
				
				for(int i = 0; i < reportAction.length; i++){
					if(i == reportAction.length - 1){
						strQuery.append(" sum(case when rcsd_action = '" + reportAction[i] + "' then 1 else 0 end) as col_" + (i+1));
					}else{
						strQuery.append(" sum(case when rcsd_action = '" + reportAction[i] + "' then 1 else 0 end) as col_" +( i+1) +", ");
					}
				}	
				strQuery.append(" from rptcasestatusdaily ");
				strQuery.append(" left join tbluser on (rptcasestatusdaily.rcsd_created_by = tbluser.login_id) ");
				strQuery.append(" inner join tblaccessrole on (tbluser.role_code = tblaccessrole.role_code) ");
				strQuery.append(" where rcsd_status in( select distinct s_next from statusmast 	where s_next not like 'CLOSE%') ");
				strQuery.append(q_search1);
				strQuery.append(" group by rcsd_created_by, name, role_name");
				strQuery.append(" union ");
				strQuery.append(" select rcsd_created_by, count(rcsd_case_id) as total, name, role_name, ");
				
				for(int i = 0; i < reportAction.length; i++){
					if(i == reportAction.length - 1){
						strQuery.append(" sum(case when rcsd_action = '" + reportAction[i] + "' then 1 else 0 end) as col_" + (i+1));
					}else{
						strQuery.append(" sum(case when rcsd_action = '" + reportAction[i] + "' then 1 else 0 end) as col_" +( i+1) +", ");
					}
				}
				
				strQuery.append(" from rptcasestatusdaily ");
				strQuery.append(" left join tbluser on (rptcasestatusdaily.rcsd_created_by = tbluser.login_id) ");
				strQuery.append(" inner join tblaccessrole on (tbluser.role_code = tblaccessrole.role_code) ");
				strQuery.append(" where rcsd_status in( select distinct s_next from statusmast 	where s_next not like 'CLOSE%') ");
				strQuery.append(q_search2);
				strQuery.append(" group by rcsd_created_by, name, role_name ");
								
				//PreparedStatement statement = conn.prepareStatement(sql);
				PreparedStatement statement = conn.prepareStatement(strQuery.toString());
				
				try {
					
					int last_index = 0;
					
					if(search != null) {
						if(search.containsKey("from_date") && search.get("from_date").length() > 0 && search.containsKey("to_date") && search.get("to_date").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("to_date"));
							++last_index;
							statement.setString(last_index, search.get("from_date"));
							++last_index;
							statement.setString(last_index, search.get("to_date"));
						}
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							
							TreeMap<String, String> c = new TreeMap<String, String>();
							
							//int total_work = resultSet.getInt("col_a") + resultSet.getInt("col_b") + resultSet.getInt("col_c") + resultSet.getInt("col_d") + resultSet.getInt("col_e") + resultSet.getInt("col_f") + resultSet.getInt("col_g");
							int total_work = 0;
							for(int i = 0; i < reportAction.length; i++){
								total_work += resultSet.getInt("col_" + (i+1));
							}
							
							c.put("creator", resultSet.getString("rcsd_created_by"));
							c.put("creator_name", resultSet.getString("name"));
							c.put("role_name", resultSet.getString("role_name"));
							c.put("total_applications", resultSet.getString("total"));
							/*c.put("col_a", resultSet.getString("col_a"));
							c.put("col_b", resultSet.getString("col_b"));
							c.put("col_c", resultSet.getString("col_c"));
							c.put("col_d", resultSet.getString("col_d"));
							c.put("col_e", resultSet.getString("col_e"));
							c.put("col_f", resultSet.getString("col_f"));
							c.put("col_g", resultSet.getString("col_g"));*/
							
							for(int i = 0; i < reportAction.length; i++){
								c.put("col_" + i, resultSet.getString("col_" + (i + 1)));
							}
							c.put("total_work", Integer.toString(total_work));
							
							cArr.add(c);
						}
						
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return cArr;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static ArrayList<TreeMap<String, String>> getSalesProductivityReportMonthly(TreeMap<String, String> search) {
		ArrayList<TreeMap<String, String>> cArr = new ArrayList<TreeMap<String, String>>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
				
				if(search != null) {
					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						q_search += "and c_group_code = ? ";
					}
					
					if(search.containsKey("year") && search.get("year").length() > 0){
						q_search += "and rcs_year = ? ";
					}
					
					if(search.containsKey("month") && search.get("month").length() > 0){
						q_search += "and rcs_month = ? ";
					}
				}
				
				String sql = "select rptcasestatus.rcs_created_by, tblaccessrole.role_name, merchant.m_mcc_code, "
							+ " count(casemast.c_id) as total_application, "
							+ "	sum(case when c_type = 'New Application' and rcs_matrix = 'Matrix' then 1 else 0 end) as new_application_matrix, "
							+ " sum(case when c_type = 'New Application' and rcs_matrix = 'Non Matrix' then 1 else 0 end) as new_application_non_matrix, "
							+ " sum(case when c_type = 'Extend Facility' then 1 else 0 end) as extend_facility, "
							+ "	sum(case when c_type = 'e-Commerce/M2u' then 1 else 0 end) as e_commerce, "
							+ "	sum(case when c_type = 'New Application' and rcs_matrix is null then 1 else 0 end) as new_application_wo_matrix, "
							+ " sum(case when c_type = 'Additional Terminal' then 1 else 0 end) as additional_terminal, "
							+ " sum(case when rcs_status in ("
							+ "		select distinct s_next "
							+ "		from statusmast "
							+ "		where s_next not like 'CLOSE%') then 1 else 0 end) as pending_completion, "
							+ "	sum(case when rcs_status in ("
							+ "		select distinct s_next "
							+ "		from statusmast "
							+ "		where s_next like 'CLOSE%') then 1 else 0 end) as closed "
							+ "from rptcasestatus "
							+ "left join casemast on rptcasestatus.rcs_case_id = casemast.c_id "
							+ "left join tblaccessrole on casemast.c_group_code = tblaccessrole.role_code "
							+ "left join merchant on casemast.c_merchant_id = merchant.m_id "
							+ "where 1 = 1 "
							+ q_search
							+ "group by rptcasestatus.rcs_created_by, tblaccessrole.role_name, merchant.m_mcc_code";
							
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					
					int last_index = 0;
					
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("group_code"));
						}
						
						if(search.containsKey("year") && search.get("year").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("year"));
						}
						
						if(search.containsKey("month") && search.get("month").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("month"));
						}
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							
							TreeMap<String, String> c = new TreeMap<String, String>();
							
							int total_a = resultSet.getInt("new_application_matrix") + resultSet.getInt("new_application_non_matrix") + resultSet.getInt("extend_facility") + resultSet.getInt("additional_terminal") + resultSet.getInt("e_commerce");
							int total_b = resultSet.getInt("pending_completion") + resultSet.getInt("closed");
							
							c.put("creator", resultSet.getString("rcs_created_by"));
							c.put("group_name", resultSet.getString("role_name"));
							c.put("total_application", resultSet.getString("total_application"));
							c.put("mcc", resultSet.getString("m_mcc_code"));
							c.put("new_application_matrix", resultSet.getString("new_application_matrix"));
							c.put("new_application_non_matrix", resultSet.getString("new_application_non_matrix"));
							c.put("extend_facility", resultSet.getString("extend_facility"));
							c.put("additional_terminal", resultSet.getString("additional_terminal"));
							c.put("e_commerce", resultSet.getString("e_commerce"));
							c.put("total_a", Integer.toString(total_a));
							c.put("pending_completion", resultSet.getString("pending_completion"));
							c.put("closed", resultSet.getString("closed"));
							c.put("total_b", Integer.toString(total_b));
							
							cArr.add(c);
						}
						
					} finally {
						resultSet.close();
					}
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return cArr;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static ArrayList<TreeMap<String, String>> getSalesProductivitySummaryMonthly(TreeMap<String, String> search) {
		ArrayList<TreeMap<String, String>> cArr = new ArrayList<TreeMap<String, String>>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
				
				if(search != null) {
					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						q_search += "and c_group_code = ? ";
					}
					
					if(search.containsKey("year") && search.get("year").length() > 0){
						q_search += "and rcs_year = ? ";
					}
					
					if(search.containsKey("month") && search.get("month").length() > 0){
						q_search += "and rcs_month = ? ";
					}
				}
				
				String sql = "select tblaccessrole.role_name, "
							+ " sum(case when rcs_status in ("
							+ "		select distinct s_next "
							+ "		from statusmast "
							+ "		where s_next not like 'CLOSE%') then 1 else 0 end) as pending_completion, "
							+ "	sum(case when rcs_status in ("
							+ "		select distinct s_next "
							+ "		from statusmast "
							+ "		where s_next like 'CLOSE%') then 1 else 0 end) as closed "
							+ "from rptcasestatus "
							+ "left join casemast on rptcasestatus.rcs_case_id = casemast.c_id "
							+ "left join tblaccessrole on casemast.c_group_code = tblaccessrole.role_code "
							+ "where 1 = 1 "
							+ q_search
							+ "group by tblaccessrole.role_name";
							
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					
					int last_index = 0;
					
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("group_code"));
						}
						
						if(search.containsKey("year") && search.get("year").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("year"));
						}
						
						if(search.containsKey("month") && search.get("month").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("month"));
						}
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							
							TreeMap<String, String> c = new TreeMap<String, String>();
							
							int total = resultSet.getInt("pending_completion") + resultSet.getInt("closed");
							
							c.put("group_name", resultSet.getString("role_name"));
							c.put("total", Integer.toString(total));
							
							cArr.add(c);
						}
						
					} finally {
						resultSet.close();
					}
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return cArr;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static ArrayList<TreeMap<String, String>> getSalesProductivityReportDaily(TreeMap<String, String> search) {
		ArrayList<TreeMap<String, String>> cArr = new ArrayList<TreeMap<String, String>>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
				String q_search2 = "";
				
				if(search != null) {
					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						q_search += "and c_group_code = ? ";
					}
					
				if(search.containsKey("from_date") && search.get("from_date").length() > 0 && search.containsKey("to_date") && search.get("to_date").length() > 0){
						q_search += "and rcsd_date = to_date(?, 'DD/MM/YYYY') ";
						q_search2 += "and rcsd_date >= to_date(?, 'DD/MM/YYYY') "
									+ "and rcsd_date <= to_date(?, 'DD/MM/YYYY') ";
					}
				}
				
				String sql = "select rptcasestatusdaily.rcsd_created_by, tblaccessrole.role_name, merchant.m_mcc_code, "
							+ " count(casemast.c_id) as total_application, "
							+ "	sum(case when c_type = 'New Application' and rcsd_matrix = 'Matrix' then 1 else 0 end) as new_application_matrix, "
							+ " sum(case when c_type = 'New Application' and rcsd_matrix = 'Non Matrix' then 1 else 0 end) as new_application_non_matrix, "
							+ " sum(case when c_type = 'Extend Facility' then 1 else 0 end) as extend_facility, "
							+ "	sum(case when c_type = 'e-Commerce/M2u' then 1 else 0 end) as e_commerce, "
							+ "	sum(case when c_type = 'New Application' and rcsd_matrix is null then 1 else 0 end) as new_application_wo_matrix, "
							+ " sum(case when c_type = 'Additional Terminal' then 1 else 0 end) as additional_terminal, "
							+ " sum(case when rcsd_status in ("
							+ "		select distinct s_next "
							+ "		from statusmast "
							+ "		where s_next not like 'CLOSE%') then 1 else 0 end) as pending_completion, "
							+ "	sum(case when rcsd_status in ("
							+ "		select distinct s_next "
							+ "		from statusmast "
							+ "		where s_next like 'CLOSE%') then 1 else 0 end) as closed "
							+ "from rptcasestatusdaily "
							+ "left join casemast on rptcasestatusdaily.rcsd_case_id = casemast.c_id "
							+ "left join tblaccessrole on casemast.c_group_code = tblaccessrole.role_code "
							+ "left join merchant on casemast.c_merchant_id = merchant.m_id "
							+ "where rcsd_status in ("
							+ "		select distinct s_next "
							+ "		from statusmast "
							+ "		where s_next not like 'CLOSE%') "
							+ q_search
							+ "group by rptcasestatusdaily.rcsd_created_by, tblaccessrole.role_name, merchant.m_mcc_code "
							
							+ "union "
							
							+"select rptcasestatusdaily.rcsd_created_by, tblaccessrole.role_name, merchant.m_mcc_code, "
							+ " count(casemast.c_id) as total_application, "
							+ "	sum(case when c_type = 'New Application' and rcsd_matrix = 'Matrix' then 1 else 0 end) as new_application_matrix, "
							+ " sum(case when c_type = 'New Application' and rcsd_matrix = 'Non Matrix' then 1 else 0 end) as new_application_non_matrix, "
							+ " sum(case when c_type = 'Extend Facility' then 1 else 0 end) as extend_facility, "
							+ "	sum(case when c_type = 'e-Commerce/M2u' then 1 else 0 end) as e_commerce, "
							+ "	sum(case when c_type = 'New Application' and rcsd_matrix is null then 1 else 0 end) as new_application_wo_matrix, "
							+ " sum(case when c_type = 'Additional Terminal' then 1 else 0 end) as additional_terminal, "
							+ " sum(case when rcsd_status in ("
							+ "		select distinct s_next "
							+ "		from statusmast "
							+ "		where s_next not like 'CLOSE%') then 1 else 0 end) as pending_completion, "
							+ "	sum(case when rcsd_status in ("
							+ "		select distinct s_next "
							+ "		from statusmast "
							+ "		where s_next like 'CLOSE%') then 1 else 0 end) as closed "
							+ "from rptcasestatusdaily "
							+ "left join casemast on rptcasestatusdaily.rcsd_case_id = casemast.c_id "
							+ "left join tblaccessrole on casemast.c_group_code = tblaccessrole.role_code "
							+ "left join merchant on casemast.c_merchant_id = merchant.m_id "
							+ "where rcsd_status in ("
							+ "		select distinct s_next "
							+ "		from statusmast "
							+ "		where s_next like 'CLOSE%') "
							+ q_search2
							+ "group by rptcasestatusdaily.rcsd_created_by, tblaccessrole.role_name, merchant.m_mcc_code ";
								
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					
					int last_index = 0;
					
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("group_code"));
						}
						
						if(search.containsKey("from_date") && search.get("from_date").length() > 0 && search.containsKey("to_date") && search.get("to_date").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("to_date"));
							++last_index;
							statement.setString(last_index, search.get("from_date"));
							++last_index;
							statement.setString(last_index, search.get("to_date"));
						}
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							
							TreeMap<String, String> c = new TreeMap<String, String>();
							
							int total_a = resultSet.getInt("new_application_matrix") + resultSet.getInt("new_application_non_matrix") + resultSet.getInt("extend_facility") + resultSet.getInt("additional_terminal") + resultSet.getInt("e_commerce");
							int total_b = resultSet.getInt("pending_completion") + resultSet.getInt("closed");
							
							c.put("creator", resultSet.getString("rcsd_created_by"));
							c.put("group_name", resultSet.getString("role_name"));
							c.put("total_application", resultSet.getString("total_application"));
							c.put("mcc", resultSet.getString("m_mcc_code"));
							c.put("new_application_matrix", resultSet.getString("new_application_matrix"));
							c.put("new_application_non_matrix", resultSet.getString("new_application_non_matrix"));
							c.put("extend_facility", resultSet.getString("extend_facility"));
							c.put("additional_terminal", resultSet.getString("additional_terminal"));
							c.put("e_commerce", resultSet.getString("e_commerce"));
							c.put("total_a", Integer.toString(total_a));
							c.put("pending_completion", resultSet.getString("pending_completion"));
							c.put("closed", resultSet.getString("closed"));
							c.put("total_b", Integer.toString(total_b));
							
							cArr.add(c);
						}
						
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return cArr;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static ArrayList<TreeMap<String, String>> getSalesProductivitySummaryDaily(TreeMap<String, String> search) {
		ArrayList<TreeMap<String, String>> cArr = new ArrayList<TreeMap<String, String>>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
				String q_search2 = "";
				
				if(search != null) {
					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						q_search += "and c_group_code = ? ";
					}
					
				if(search.containsKey("from_date") && search.get("from_date").length() > 0 && search.containsKey("to_date") && search.get("to_date").length() > 0){
						q_search += "and rcsd_date = to_date(?, 'DD/MM/YYYY') ";
						q_search2 += "and rcsd_date >= to_date(?, 'DD/MM/YYYY') "
									+ "and rcsd_date <= to_date(?, 'DD/MM/YYYY') ";
					}
				}
				
				String sql = "select tblaccessrole.role_name, "
							+ " sum(case when rcsd_status in ("
							+ "		select distinct s_next "
							+ "		from statusmast "
							+ "		where s_next not like 'CLOSE%') then 1 else 0 end) as pending_completion, "
							+ "	sum(case when rcsd_status in ("
							+ "		select distinct s_next "
							+ "		from statusmast "
							+ "		where s_next like 'CLOSE%') then 1 else 0 end) as closed "
							+ "from rptcasestatusdaily "
							+ "left join casemast on rptcasestatusdaily.rcsd_case_id = casemast.c_id "
							+ "left join tblaccessrole on casemast.c_group_code = tblaccessrole.role_code "
							+ "where rcsd_status in ("
							+ "		select distinct s_next "
							+ "		from statusmast "
							+ "		where s_next not like 'CLOSE%') "
							+ q_search
							+ "group by tblaccessrole.role_name "
							
							+ "union "
							
							+"select tblaccessrole.role_name, "
							+ " sum(case when rcsd_status in ("
							+ "		select distinct s_next "
							+ "		from statusmast "
							+ "		where s_next not like 'CLOSE%') then 1 else 0 end) as pending_completion, "
							+ "	sum(case when rcsd_status in ("
							+ "		select distinct s_next "
							+ "		from statusmast "
							+ "		where s_next like 'CLOSE%') then 1 else 0 end) as closed "
							+ "from rptcasestatusdaily "
							+ "left join casemast on rptcasestatusdaily.rcsd_case_id = casemast.c_id "
							+ "left join tblaccessrole on casemast.c_group_code = tblaccessrole.role_code "
							+ "where rcsd_status in ("
							+ "		select distinct s_next "
							+ "		from statusmast "
							+ "		where s_next like 'CLOSE%') "
							+ q_search2
							+ "group by tblaccessrole.role_name ";
								
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					
					int last_index = 0;
					
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("group_code"));
						}
						
						if(search.containsKey("from_date") && search.get("from_date").length() > 0 && search.containsKey("to_date") && search.get("to_date").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("to_date"));
							++last_index;
							statement.setString(last_index, search.get("from_date"));
							++last_index;
							statement.setString(last_index, search.get("to_date"));
						}
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							
							TreeMap<String, String> c = new TreeMap<String, String>();
							
							int total = resultSet.getInt("pending_completion") + resultSet.getInt("closed");
							
							c.put("group_name", resultSet.getString("role_name"));
							c.put("total", Integer.toString(total));
							
							cArr.add(c);
						}
						
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return cArr;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static ArrayList<TreeMap<String, String>> getClosedReportMonthly(TreeMap<String, String> search) {
		ArrayList<TreeMap<String, String>> cArr = new ArrayList<TreeMap<String, String>>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
				
				if(search != null) {
					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						q_search += "and c_group_code = ? ";
					}
					
					if(search.containsKey("application_type") && search.get("application_type").length() > 0){
						q_search += "and c_type = ? ";
					}
					
					if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
						q_search += "and c_reseller_location = ? ";
					}
					
					if(search.containsKey("year") && search.get("year").length() > 0){
						q_search += "and rcs_year = ? ";
					}
					
					if(search.containsKey("month") && search.get("month").length() > 0){
						q_search += "and rcs_month = ? ";
					}
				}
				
				String sql = "select rptcasestatus.*, casemast.*, tblaccessrole.role_name, tbluser.name "
							+ "from rptcasestatus "
							+ "left join casemast on rptcasestatus.rcs_case_id = casemast.c_id "
							+ "left join tblaccessrole on role_code = c_group_code "
							+ "left join tbluser on login_id = c_created_by "
							+ "where rcs_status in "
							+ "( select distinct s_next from statusmast"
							+ "  where s_next like 'CLOSE%' ) "
							+ q_search;
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					
					int last_index = 0;
					
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("group_code"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("reseller_location"));
						}
						
						if(search.containsKey("year") && search.get("year").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("year"));
						}
						
						if(search.containsKey("month") && search.get("month").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("month"));
						}
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							
							TreeMap<String, String> c = new TreeMap<String, String>();
							
							long created_date = ((Timestamp) resultSet.getTimestamp("c_created_date")).getTime();
							long modified_date = ((Timestamp) resultSet.getTimestamp("c_modified_date")).getTime();
							
							String crtd_date = Casemast.formatCreatedDate("dd/MM/yyyy HH:mm:ss", created_date);
							String mdfd_date = Casemast.formatCreatedDate("dd/MM/yyyy HH:mm:ss", modified_date);
							
							c.put("map_ref_no", resultSet.getString("c_reference_no"));
							c.put("application_type", resultSet.getString("c_type"));
							c.put("created_date", crtd_date);
							c.put("modified_date", mdfd_date);
							c.put("matrix", resultSet.getString("rcs_matrix"));
							c.put("group_code", resultSet.getString("role_name") == null ? resultSet.getString("c_group_code") : resultSet.getString("role_name"));
							c.put("creator", resultSet.getString("name") == null ? resultSet.getString("c_created_by") : resultSet.getString("name"));
							c.put("region", resultSet.getString("c_reseller_location"));
							c.put("status", resultSet.getString("rcs_status"));
							
							cArr.add(c);
						}
						
					} finally {
						resultSet.close();
					}
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return cArr;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static ArrayList<TreeMap<String, String>> getClosedSummaryMonthly(TreeMap<String, String> search) {
		ArrayList<TreeMap<String, String>> cArr = new ArrayList<TreeMap<String, String>>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
				
				if(search != null) {
					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						q_search += "and c_group_code = ? ";
					}
					
					if(search.containsKey("application_type") && search.get("application_type").length() > 0){
						q_search += "and c_type = ? ";
					}
					
					if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
						q_search += "and c_reseller_location = ? ";
					}
					
					if(search.containsKey("year") && search.get("year").length() > 0){
						q_search += "and rcs_year = ? ";
					}
					
					if(search.containsKey("month") && search.get("month").length() > 0){
						q_search += "and rcs_month = ? ";
					}
				}
				
				String sql = "select rptcasestatus.rcs_status, "
							+ "count(rcs_id) as total "
							+ "from rptcasestatus "
							+ "left join casemast on rptcasestatus.rcs_case_id = casemast.c_id "
							+ "where rcs_status in "
							+ "( select distinct s_next from statusmast"
							+ "  where s_next like 'CLOSE%' ) "
							+ q_search
							+ " group by rcs_status";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					
					int last_index = 0;
					
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("group_code"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("reseller_location"));
						}
						
						if(search.containsKey("year") && search.get("year").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("year"));
						}
						
						if(search.containsKey("month") && search.get("month").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("month"));
						}
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							
							TreeMap<String, String> c = new TreeMap<String, String>();
							
							c.put("status", resultSet.getString("rcs_status"));
							c.put("total", resultSet.getString("total"));
							
							cArr.add(c);
						}
						
					} finally {
						resultSet.close();
					}
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return cArr;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static ArrayList<TreeMap<String, String>> getClosedReportDaily(TreeMap<String, String> search) {
		ArrayList<TreeMap<String, String>> cArr = new ArrayList<TreeMap<String, String>>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
				
				if(search != null) {
					
					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						q_search += "and c_group_code = ? ";
					}
					
					if(search.containsKey("application_type") && search.get("application_type").length() > 0){
						q_search += "and c_type = ? ";
					}
					
					if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
						q_search += "and c_reseller_location = ? ";
					}
					
					if(search.containsKey("from_date") && search.get("from_date").length() > 0 && search.containsKey("to_date") && search.get("to_date").length() > 0){
						
						q_search += "and rcsd_date >= to_date(?, 'DD/MM/YYYY') "
									+ "and rcsd_date <= to_date(?, 'DD/MM/YYYY') ";
					}
				}
				
				String sql = "select rptcasestatusdaily.*, casemast.*, tblaccessrole.role_name, tbluser.name "
						+ "from rptcasestatusdaily "
						+ "left join casemast on rptcasestatusdaily.rcsd_case_id = casemast.c_id "
						+ "left join tblaccessrole on role_code = c_group_code "
						+ "left join tbluser on login_id = c_created_by "
						+ "where rcsd_status in "
						+ "( select distinct s_next from statusmast"
						+ "  where s_next like 'CLOSE%' ) "
						+ q_search;
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					
					int last_index = 0;
					
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("group_code"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("reseller_location"));
						}
						
						if(search.containsKey("from_date") && search.get("from_date").length() > 0 && search.containsKey("to_date") && search.get("to_date").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("from_date"));
							++last_index;
							statement.setString(last_index, search.get("to_date"));
						}
						
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							TreeMap<String, String> c = new TreeMap<String, String>();
							
							long created_date = ((Timestamp) resultSet.getTimestamp("c_created_date")).getTime();
							long modified_date = ((Timestamp) resultSet.getTimestamp("c_modified_date")).getTime();
							
							String crtd_date = Casemast.formatCreatedDate("dd/MM/yyyy HH:mm:ss", created_date);
							String mdfd_date = Casemast.formatCreatedDate("dd/MM/yyyy HH:mm:ss", modified_date);
							
							c.put("map_ref_no", resultSet.getString("c_reference_no"));
							c.put("application_type", resultSet.getString("c_type"));
							c.put("created_date", crtd_date);
							c.put("modified_date", mdfd_date);
							c.put("matrix", resultSet.getString("rcsd_matrix"));
							c.put("group_code", resultSet.getString("role_name") == null ? resultSet.getString("c_group_code") : resultSet.getString("role_name"));
							c.put("creator", resultSet.getString("name") == null ? resultSet.getString("c_created_by") : resultSet.getString("name"));
							c.put("region", resultSet.getString("c_reseller_location"));
							c.put("status", resultSet.getString("rcsd_status"));
							
							cArr.add(c);
						}
						
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return cArr;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static ArrayList<TreeMap<String, String>> getClosedSummaryDaily(TreeMap<String, String> search) {
		ArrayList<TreeMap<String, String>> cArr = new ArrayList<TreeMap<String, String>>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
				
				if(search != null) {
					
					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						q_search += "and c_group_code = ? ";
					}
					
					if(search.containsKey("application_type") && search.get("application_type").length() > 0){
						q_search += "and c_type = ? ";
					}
					
					if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
						q_search += "and c_reseller_location = ? ";
					}
					
					if(search.containsKey("from_date") && search.get("from_date").length() > 0 && search.containsKey("to_date") && search.get("to_date").length() > 0){
						
						q_search += "and rcsd_date >= to_date(?, 'DD/MM/YYYY') "
									+ "and rcsd_date <= to_date(?, 'DD/MM/YYYY') ";
					}
				}
				
				String sql = "select rptcasestatusdaily.rcsd_status, "
						+ "count(rcsd_id) as total "
						+ "from rptcasestatusdaily "
						+ "left join casemast on rptcasestatusdaily.rcsd_case_id = casemast.c_id "
						+ "where rcsd_status in "
						+ "( select distinct s_next from statusmast"
						+ "  where s_next like 'CLOSE%' ) "
						+ q_search
						+ "group by rcsd_status ";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					
					int last_index = 0;
					
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("group_code"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("reseller_location"));
						}
						
						if(search.containsKey("from_date") && search.get("from_date").length() > 0 && search.containsKey("to_date") && search.get("to_date").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("from_date"));
							++last_index;
							statement.setString(last_index, search.get("to_date"));
						}
						
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							TreeMap<String, String> c = new TreeMap<String, String>();
							
							c.put("status", resultSet.getString("rcsd_status"));
							c.put("total", resultSet.getString("total"));
							
							cArr.add(c);
						}
						
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return cArr;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static ArrayList<TreeMap<String, String>> getExceptionalReportMonthly(TreeMap<String, String> search) {
		ArrayList<TreeMap<String, String>> cArr = new ArrayList<TreeMap<String, String>>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
				
				if(search != null) {
					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						q_search += "and c_group_code = ? ";
					}
					
					if(search.containsKey("application_type") && search.get("application_type").length() > 0){
						q_search += "and c_type = ? ";
					}
					
					if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
						q_search += "and c_reseller_location = ? ";
					}
					
					if(search.containsKey("year") && search.get("year").length() > 0){
						q_search += "and rcs_year = ? ";
					}
					
					if(search.containsKey("month") && search.get("month").length() > 0){
						q_search += "and rcs_month = ? ";
					}
				}
				
				String sql = "select rptcasestatus.*, casemast.*, tblaccessrole.role_name, tbluser.name "
							+ "from rptcasestatus "
							+ "left join casemast on rptcasestatus.rcs_case_id = casemast.c_id "
							+ "left join tblaccessrole on role_code = c_group_code "
							+ "left join tbluser on login_id = c_created_by "
							+ "where ("
							+ "rcs_status = 'PENDING DOCUMENT FOR CREDIT CHECK'"
							+ "or rcs_status = 'PENDING DECISION, RMS NOT RECOMMENDED'"
							+ "or rcs_status = 'PENDING DOC / INFO FOR MID SETUP') "
							+ q_search;
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					
					int last_index = 0;
					
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("group_code"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("reseller_location"));
						}
						
						if(search.containsKey("year") && search.get("year").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("year"));
						}
						
						if(search.containsKey("month") && search.get("month").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("month"));
						}
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							
							TreeMap<String, String> c = new TreeMap<String, String>();
							
							long created_date = ((Timestamp) resultSet.getTimestamp("c_created_date")).getTime();
							
							String crtd_date = Casemast.formatCreatedDate("dd/MM/yyyy HH:mm:ss", created_date);
							
							c.put("map_ref_no", resultSet.getString("c_reference_no"));
							c.put("application_type", resultSet.getString("c_type"));
							c.put("created_date", crtd_date);
							c.put("matrix", resultSet.getString("rcs_matrix"));
							c.put("group_code", resultSet.getString("role_name") == null ? resultSet.getString("c_group_code") : resultSet.getString("role_name"));
							c.put("creator", resultSet.getString("name") == null ? resultSet.getString("c_created_by") : resultSet.getString("name"));
							c.put("region", resultSet.getString("c_reseller_location"));
							c.put("status", resultSet.getString("rcs_status"));
							
							cArr.add(c);
						}
						
					} finally {
						resultSet.close();
					}
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return cArr;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static ArrayList<TreeMap<String, String>> getExceptionalSummaryMonthly(TreeMap<String, String> search) {
		ArrayList<TreeMap<String, String>> cArr = new ArrayList<TreeMap<String, String>>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
				
				if(search != null) {
					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						q_search += "and c_group_code = ? ";
					}
					
					if(search.containsKey("application_type") && search.get("application_type").length() > 0){
						q_search += "and c_type = ? ";
					}
					
					if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
						q_search += "and c_reseller_location = ? ";
					}
					
					if(search.containsKey("year") && search.get("year").length() > 0){
						q_search += "and rcs_year = ? ";
					}
					
					if(search.containsKey("month") && search.get("month").length() > 0){
						q_search += "and rcs_month = ? ";
					}
				}
				
				String sql = "select rcs_status, "
							+ "count(rcs_id) as total "
							+ "from rptcasestatus "
							+ "left join casemast on rptcasestatus.rcs_case_id = casemast.c_id "
							+ "where ("
							+ "rcs_status = 'PENDING DOCUMENT FOR CREDIT CHECK'"
							+ "or rcs_status = 'PENDING DECISION, RMS NOT RECOMMENDED'"
							+ "or rcs_status = 'PENDING DOC / INFO FOR MID SETUP') "
							+ q_search
							+ " group by rcs_status";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					
					int last_index = 0;
					
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("group_code"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("reseller_location"));
						}
						
						if(search.containsKey("year") && search.get("year").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("year"));
						}
						
						if(search.containsKey("month") && search.get("month").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("month"));
						}
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							
							TreeMap<String, String> c = new TreeMap<String, String>();
	
							c.put("status", resultSet.getString("rcs_status"));
							c.put("total", resultSet.getString("total"));
							
							cArr.add(c);
						}
						String[] listStatus = {"PENDING DOCUMENT FOR CREDIT CHECK","PENDING DECISION, RMS NOT RECOMMENDED","PENDING DOC / INFO FOR MID SETUP"};
						if (cArr.size() <= 0) {
							for (int i = 0; i < listStatus.length; i++) {
								TreeMap<String, String> c1 = new TreeMap<String, String>();
								c1.put("status", listStatus[i]);
								c1.put("total", "0");
								cArr.add(c1);
							}
						} else {
							ArrayList<Integer> listIndex = new ArrayList<Integer>();
							for(TreeMap<String, String> c : cArr){
								for (int i = 0; i < listStatus.length; i++) {
									if (c.get("status").equals(listStatus[i])) {
										listIndex.add(i);
										break;
									}
								}
							}
							for(int i = 0; i < listIndex.size(); i++){
								TreeMap<String, String> c1 = new TreeMap<String, String>();
								c1.put("status", listStatus[listIndex.get(i)]);
								c1.put("total", "0");
								cArr.add(c1);
							}
						}
						
					} finally {
						resultSet.close();
					}
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return cArr;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static ArrayList<TreeMap<String, String>> getExceptionalReportDaily(TreeMap<String, String> search) {
		ArrayList<TreeMap<String, String>> cArr = new ArrayList<TreeMap<String, String>>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
				
				if(search != null) {
					
					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						q_search += "and c_group_code = ? ";
					}
					
					if(search.containsKey("application_type") && search.get("application_type").length() > 0){
						q_search += "and c_type = ? ";
					}
					
					if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
						q_search += "and c_reseller_location = ? ";
					}
					
					if(search.containsKey("from_date") && search.get("from_date").length() > 0 && search.containsKey("to_date") && search.get("to_date").length() > 0){
						
						q_search += "and rcsd_date >= to_date(?, 'DD/MM/YYYY') "
									+ "and rcsd_date <= to_date(?, 'DD/MM/YYYY') ";
					}
				}
				
				String sql = "select rptcasestatusdaily.*, casemast.*, tblaccessrole.role_name, tbluser.name "
							+ "from rptcasestatusdaily "
							+ "left join casemast on rptcasestatusdaily.rcsd_case_id = casemast.c_id "
							+ "left join tblaccessrole on role_code = c_group_code "
							+ "left join tbluser on login_id = c_created_by "
							+ "where ("
							+ "rcsd_status = 'PENDING DOCUMENT FOR CREDIT CHECK' "
							+ "or rcsd_status = 'PENDING DECISION, RMS NOT RECOMMENDED' "
							+ "or rcsd_status = 'PENDING DOC / INFO FOR MID SETUP') "
							+ q_search;
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					
					int last_index = 0;
					
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("group_code"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("reseller_location"));
						}
						
						if(search.containsKey("from_date") && search.get("from_date").length() > 0 && search.containsKey("to_date") && search.get("to_date").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("from_date"));
							++last_index;
							statement.setString(last_index, search.get("to_date"));
						}
						
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							TreeMap<String, String> c = new TreeMap<String, String>();
							
							long created_date = ((Timestamp) resultSet.getTimestamp("c_created_date")).getTime();
							
							String crtd_date = Casemast.formatCreatedDate("dd/MM/yyyy HH:mm:ss", created_date);
							
							c.put("map_ref_no", resultSet.getString("c_reference_no"));
							c.put("application_type", resultSet.getString("c_type"));
							c.put("created_date", crtd_date);
							c.put("matrix", resultSet.getString("rcsd_matrix"));
							c.put("group_code", resultSet.getString("role_name") == null ? resultSet.getString("c_group_code") : resultSet.getString("role_name"));
							c.put("creator", resultSet.getString("name") == null ? resultSet.getString("c_created_by") : resultSet.getString("name"));
							c.put("region", resultSet.getString("c_reseller_location"));
							c.put("status", resultSet.getString("rcsd_status"));
							
							cArr.add(c);
						}
						
					} catch (Exception e) {
						e.printStackTrace();
					}  finally {
						resultSet.close();
					}
				
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					statement.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}  finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return cArr;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static ArrayList<TreeMap<String, String>> getExceptionalSummaryDaily(TreeMap<String, String> search) {
		ArrayList<TreeMap<String, String>> cArr = new ArrayList<TreeMap<String, String>>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
				
				if(search != null) {
					
					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						q_search += "and c_group_code = ? ";
					}
					
					if(search.containsKey("application_type") && search.get("application_type").length() > 0){
						q_search += "and c_type = ? ";
					}
					
					if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
						q_search += "and c_reseller_location = ? ";
					}
					
					if(search.containsKey("from_date") && search.get("from_date").length() > 0 && search.containsKey("to_date") && search.get("to_date").length() > 0){
						
						q_search += "and rcsd_date >= to_date(?, 'DD/MM/YYYY') "
									+ "and rcsd_date <= to_date(?, 'DD/MM/YYYY') ";
					}
				}
				
				String sql = "select rcsd_status, "
							+ "count(rcsd_id) as total "
							+ "from rptcasestatusdaily "
							+ "left join casemast on rptcasestatusdaily.rcsd_case_id = casemast.c_id "
							+ "where ("
							+ "rcsd_status = 'PENDING DOCUMENT FOR CREDIT CHECK'"
							+ "or rcsd_status = 'PENDING DECISION, RMS NOT RECOMMENDED'"
							+ "or rcsd_status = 'PENDING DOC / INFO FOR MID SETUP') "
							+ q_search
							+ " group by rcsd_status";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					
					int last_index = 0;
					
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("group_code"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("reseller_location"));
						}
						
						if(search.containsKey("from_date") && search.get("from_date").length() > 0 && search.containsKey("to_date") && search.get("to_date").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("from_date"));
							++last_index;
							statement.setString(last_index, search.get("to_date"));
						}
						
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						
						while(resultSet.next()){
							TreeMap<String, String> c = new TreeMap<String, String>();
							c.put("status", resultSet.getString("rcsd_status"));
							c.put("total", resultSet.getString("total"));
							
							cArr.add(c);
						}
						String[] listStatus = {"PENDING DOCUMENT FOR CREDIT CHECK","PENDING DECISION, RMS NOT RECOMMENDED","PENDING DOC / INFO FOR MID SETUP"};
						if (cArr.size() <= 0) {
							for (int i = 0; i < listStatus.length; i++) {
								TreeMap<String, String> c1 = new TreeMap<String, String>();
								c1.put("status", listStatus[i]);
								c1.put("total", "0");
								cArr.add(c1);
							}
						} else {
							ArrayList<Integer> listIndex = new ArrayList<Integer>();
							for(TreeMap<String, String> c : cArr){
								for (int i = 0; i < listStatus.length; i++) {
									if (c.get("status").equals(listStatus[i])) {
										listIndex.add(i);
										break;
									}
								}
							}
							for(int i = 0; i < listIndex.size(); i++){
								TreeMap<String, String> c1 = new TreeMap<String, String>();
								c1.put("status", listStatus[listIndex.get(i)]);
								c1.put("total", "0");
								cArr.add(c1);
							}
						}
					} finally {
						resultSet.close();
					}
				
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return cArr;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static ArrayList<TreeMap<String, String>> getSLAIndividualReport(TreeMap<String, String> search) {
		ArrayList<TreeMap<String, String>> cArr = new ArrayList<TreeMap<String, String>>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
		
				if(search != null) {
					
					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						q_search += "and c_group_code = ? ";
					}
					
					if(search.containsKey("application_type") && search.get("application_type").length() > 0){
						q_search += "and c_type = ? ";
					}
					
					if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
						q_search += "and c_reseller_location = ? ";
					}
					
					if(search.containsKey("process") && search.get("process").length() > 0){
						q_search += "and rpt_process = ? ";
					}
					
					if(search.containsKey("from_date") && search.get("from_date").length() > 0 && search.containsKey("to_date") && search.get("to_date").length() > 0){
						q_search += "and rpt_date >= to_date(?, 'DD/MM/YYYY') "
									+ "and rpt_date <= to_date(?, 'DD/MM/YYYY') ";
					}
				}
				
				String sql = "select rptslabyindividual.*, casemast.*, tblaccessrole.role_name, tbluser.name "
							+ "from rptslabyindividual "
							+ "left join casemast on rptslabyindividual.rpt_case_id = casemast.c_id "
							+ "left join tblaccessrole on role_code = c_group_code "
							+ "left join tbluser on login_id = c_created_by " 
							+ "where 1 = 1 "
							+ q_search
							+ "order by rpt_case_id desc"; 
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					
					int last_index = 0;
					
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("group_code"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("reseller_location"));
						}
						
						if(search.containsKey("process") && search.get("process").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("process"));
						}
						
						if(search.containsKey("from_date") && search.get("from_date").length() > 0 && search.containsKey("to_date") && search.get("to_date").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("from_date"));
							++last_index;
							statement.setString(last_index, search.get("to_date"));
						}
						
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
					
						int case_id = 0;
						
						TreeMap<String, String> c = new TreeMap<String, String>();
						ArrayList<String> statuses = getStatusfromProcess2(search);
				
						while(resultSet.next()){
							
							if (resultSet.getInt("rpt_case_id") != case_id){
								/*if (case_id != 0){
									cArr.add(c);
								}*/
								
								c = new TreeMap<String, String>();
								
								
								//Reset all status to 0 SLA Days
								for (String stat : statuses){
									c.put(stat, "");
								}
								
								long created_date = ((Timestamp) resultSet.getTimestamp("c_created_date")).getTime();
								
								String crtd_date = Casemast.formatCreatedDate("dd/MM/yyyy HH:mm:ss", created_date);
								
								c.put("map_ref_no", resultSet.getString("c_reference_no"));
								c.put("application_type", resultSet.getString("c_type"));
								c.put("created_date", crtd_date);
								c.put("matrix", resultSet.getString("rpt_matrix"));
								c.put("group_code", resultSet.getString("role_name") == null ? resultSet.getString("c_group_code") : resultSet.getString("role_name"));
								c.put("creator", resultSet.getString("name") == null ? resultSet.getString("c_created_by") : resultSet.getString("name"));
								
								if("Matrix".equals(resultSet.getString("rpt_matrix")))
									c.put("region", resultSet.getString("c_reseller_location"));
								else
									c.put("region","");
								
								cArr.add(c);
								
							} 
							c.put(resultSet.getString("rpt_status") + "_s",( Casemast.formatCreatedDate("dd/MM/yyyy HH:mm",((Timestamp) resultSet.getTimestamp("rpt_submited_date")).getTime())));
							c.put(resultSet.getString("rpt_status"), resultSet.getString("rpt_sla"));
							case_id = resultSet.getInt("rpt_case_id");
						}
						
					} finally {
						resultSet.close();
					}
				
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return cArr;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static ArrayList<String> getStatusfromProcess(TreeMap<String, String> search) {
		ArrayList<String> rtn = new ArrayList<String>();
		
		try{
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
				
				if (search != null){
					if(search.containsKey("process") && search.get("process").length() > 0){
						q_search += "and s_process = ? ";
					}
				}
				
				/*String sql = "select distinct s_next "
							+ "from statusmast "
							+ "where 1 = 1 "
							+ q_search
							+ "order by s_process asc"; */
				
				String sql = "select distinct s_next "
						+ "from statusmast "
						+ "where 1 = 1 ";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					int last_index = 0;
					System.out.println(search);
					/*if (search != null){
						if(search.containsKey("process") && search.get("process").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("process"));
						}
					}*/
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							rtn.add(resultSet.getString("s_next"));
						}
					} finally {
						resultSet.close();
					}
				
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return rtn;
				
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static ArrayList<TreeMap<String, String>> getSummaryReport(TreeMap<String, String> search) {
		ArrayList<TreeMap<String, String>> cArr = new ArrayList<TreeMap<String, String>>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
		
				if(search != null) {
					
					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						q_search += "and c_group_code = ? ";
					}
					
					if(search.containsKey("application_type") && search.get("application_type").length() > 0){
						q_search += "and rpt_type = ? ";
					}
					
					if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
						q_search += "and c_reseller_location = ? ";
					}
					
					if(search.containsKey("process") && search.get("process").length() > 0){
						q_search += "and rpt_process = ? ";
					}
					
					if(search.containsKey("matrix") && search.get("matrix").length() > 0){
						q_search += "and rpt_matrix = ? ";
					}
					
					if(search.containsKey("from_date") && search.get("from_date").length() > 0 && search.containsKey("to_date") && search.get("to_date").length() > 0){
						q_search += "and rpt_date >= to_date(?, 'DD/MM/YYYY') "
									+ "and rpt_date <= to_date(?, 'DD/MM/YYYY') ";
					}
					
						
				}
				
				String sql = "select casemast.c_group_code, tblaccessrole.role_name, "
						+ "count(rptsummary.rpt_case_id) as total_submission, rpt_type, rpt_matrix, "
						+ "sum ( case when UPPER(rpt_category) = 'PRE-APPROVED' then 1 else 0 end ) as col_c, "
						+ "sum ( case when UPPER(rpt_category) = 'DOCUMENT AND CREDIT CHECK' then 1 else 0 end  ) as col_d, "
						+ "sum ( case when UPPER(rpt_category) = 'DECISIONING' then 1 else 0 end  ) as col_e, "
						+ "sum ( case when UPPER(rpt_category) = 'APPROVE, TO ISSUE NEW LETTER OF ACCEPTANCE' then 1 else 0 end  ) as col_f, "
						+ "sum ( case when UPPER(rpt_category) = 'APPROVED. REQUEST FOR MID SETUP' then 1 else 0 end  ) as col_g, "
						+ "sum ( case when UPPER(rpt_category) = 'DECLINE' then 1 else 0 end ) as col_h, "
						+ "sum ( case when UPPER(rpt_category) = 'CLOSE - CANCEL' then 1 else 0 end  ) as col_i, "
						+ "sum ( case when UPPER(rpt_category) = 'CLOSE - WITHDRAW' then 1 else 0 end  ) as col_j, "
						+ "sum ( case when UPPER(rpt_category) = 'PENDING VERIFICATION' then 1 else 0 end ) as col_k, "
						+ "sum ( case when UPPER(rpt_category) = 'PENDING INSTALLATION' then 1 else 0 end ) as col_l, "
						+ "sum ( case when UPPER(rpt_category) = 'TERMINAL INSTALLED' then 1 else 0 end ) as col_m "
						+ "from rptsummary "
						+ "left join casemast on rptsummary.rpt_case_id = casemast.c_id "
						+ "left join tblaccessrole on casemast.c_group_code = tblaccessrole.role_code "
						+ "where 1 = 1 "
						+ q_search
						+ " group by casemast.c_group_code, tblaccessrole.role_name, rpt_type, rpt_matrix"; 
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					
					int last_index = 0;
					
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("group_code"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("reseller_location"));
						}
						
						if(search.containsKey("process") && search.get("process").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("process"));
						}
						
						if(search.containsKey("matrix") && search.get("matrix").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("matrix"));
						}
						
						if(search.containsKey("from_date") && search.get("from_date").length() > 0 && search.containsKey("to_date") && search.get("to_date").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("from_date"));
							++last_index;
							statement.setString(last_index, search.get("to_date"));
						}
						
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							TreeMap<String, String> c = new TreeMap<String, String>();
							int total_work = resultSet.getInt("col_c") + resultSet.getInt("col_d") + resultSet.getInt("col_e") 
									+ resultSet.getInt("col_f") + resultSet.getInt("col_g") + resultSet.getInt("col_h") 
									+ resultSet.getInt("col_i") + resultSet.getInt("col_j") + resultSet.getInt("col_k") 
									+ resultSet.getInt("col_l") + resultSet.getInt("col_m");
							
							c.put("group", resultSet.getString("role_name") == null ? resultSet.getString("c_group_code") : resultSet.getString("role_name"));
							c.put("total_submission", resultSet.getString("total_submission"));
							c.put("col_c", resultSet.getString("col_c"));
							c.put("col_d", resultSet.getString("col_d"));
							c.put("col_e", resultSet.getString("col_e"));
							c.put("col_f", resultSet.getString("col_f"));
							c.put("col_g", resultSet.getString("col_g"));
							c.put("col_h", resultSet.getString("col_h"));
							c.put("col_i", resultSet.getString("col_i"));
							c.put("col_j", resultSet.getString("col_j"));
							c.put("col_k", resultSet.getString("col_k"));
							c.put("col_l", resultSet.getString("col_l"));
							c.put("col_m", resultSet.getString("col_m"));
							c.put("total", String.valueOf(total_work));
							
							cArr.add(c);
						}
						
					} finally {
						resultSet.close();
					}
				
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return cArr;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static TreeMap<String, ArrayList<TreeMap<String, String>>> getSummaryReport2(TreeMap<String, String> search) {
		TreeMap<String, ArrayList<TreeMap<String, String>>> result = new TreeMap<String, ArrayList<TreeMap<String,String>>>();
		
		ArrayList<TreeMap<String, String>> cArrNewAppNonMatrix = new ArrayList<TreeMap<String, String>>();
		ArrayList<TreeMap<String, String>> cArrNewAppMatrix = new ArrayList<TreeMap<String, String>>();
		ArrayList<TreeMap<String, String>> cArrNewAppOther = new ArrayList<TreeMap<String, String>>();
		ArrayList<TreeMap<String, String>> cArrECommerce = new ArrayList<TreeMap<String, String>>();
		ArrayList<TreeMap<String, String>> cArrExtend = new ArrayList<TreeMap<String, String>>();
		ArrayList<TreeMap<String, String>> cArrAddtion = new ArrayList<TreeMap<String, String>>();
		ArrayList<TreeMap<String, String>> cArrGrandTotal = new ArrayList<TreeMap<String, String>>();
		
		try {
			 int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
		     String connectionPool = ConfigManager.getInstance().getConnectionPool();
		     DBConnectionManager connMgr = DBConnectionManager.getInstance();
		     try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
		     Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
		
				if(search != null) {
					
					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						q_search += "and c_group_code = ? ";
					}
					
					if(search.containsKey("application_type") && search.get("application_type").length() > 0){
						q_search += "and rpt_type = ? ";
					}
					
					if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
						q_search += "and c_reseller_location = ? ";
					}
					
					if(search.containsKey("process") && search.get("process").length() > 0){
						q_search += "and rpt_process = ? ";
					}
					
					if(search.containsKey("matrix") && search.get("matrix").length() > 0){
						q_search += "and rpt_matrix = ? ";
					}
					
					if(search.containsKey("from_date") && search.get("from_date").length() > 0 && search.containsKey("to_date") && search.get("to_date").length() > 0){
						q_search += "and rpt_date >= to_date(?, 'DD/MM/YYYY') "
									+ "and rpt_date <= to_date(?, 'DD/MM/YYYY') ";
					}
					
						
				}

				String sql = "select casemast.c_group_code, tblaccessrole.role_name, "
						+ "count(rptsummary.rpt_case_id) as total_submission, rpt_type, rpt_matrix, "
						+ "sum ( case when UPPER(rpt_category) = 'PRE-APPROVED' then 1 else 0 end ) as col_c, "
						+ "sum ( case when UPPER(rpt_category) = 'DOCUMENT AND CREDIT CHECK' then 1 else 0 end  ) as col_d, "
						+ "sum ( case when UPPER(rpt_category) = 'DECISIONING' then 1 else 0 end  ) as col_e, "
						+ "sum ( case when UPPER(rpt_category) = 'APPROVE, TO ISSUE NEW LETTER OF ACCEPTANCE' then 1 else 0 end  ) as col_f, "
						+ "sum ( case when UPPER(rpt_category) = 'APPROVED. REQUEST FOR MID SETUP' then 1 else 0 end  ) as col_g, "
						+ "sum ( case when UPPER(rpt_category) = 'DECLINE' then 1 else 0 end ) as col_h, "
						+ "sum ( case when UPPER(rpt_category) = 'CLOSE - CANCEL' then 1 else 0 end  ) as col_i, "
						+ "sum ( case when UPPER(rpt_category) = 'CLOSE - WITHDRAW' then 1 else 0 end  ) as col_j, "
						+ "sum ( case when UPPER(rpt_category) = 'PENDING VERIFICATION' then 1 else 0 end ) as col_k, "
						+ "sum ( case when UPPER(rpt_category) = 'PENDING INSTALLATION' then 1 else 0 end ) as col_l, "
						+ "sum ( case when UPPER(rpt_category) = 'TERMINAL INSTALLED' then 1 else 0 end ) as col_m "
						+ "from rptsummary "
						+ "left join casemast on rptsummary.rpt_case_id = casemast.c_id "
						+ "left join tblaccessrole on casemast.c_group_code = tblaccessrole.role_code "
						+ "where 1 = 1 "
						+ q_search
						+ " group by casemast.c_group_code, tblaccessrole.role_name, rpt_type, rpt_matrix"; 
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					
					int last_index = 0;
					
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("group_code"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("reseller_location"));
						}
						
						if(search.containsKey("process") && search.get("process").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("process"));
						}
						
						if(search.containsKey("matrix") && search.get("matrix").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("matrix"));
						}
						
						if(search.containsKey("from_date") && search.get("from_date").length() > 0 && search.containsKey("to_date") && search.get("to_date").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("from_date"));
							++last_index;
							statement.setString(last_index, search.get("to_date"));
						}
						
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						int gTotalSummission = 0;
						int gTotalCol_C = 0;
						int gTotalCol_D = 0;
						int gTotalCol_E = 0;
						int gTotalCol_F = 0;
						int gTotalCol_G = 0;
						int gTotalCol_H = 0;
						int gTotalCol_I = 0;
						int gTotalCol_J = 0;
						int gTotalCol_K = 0;
						int gTotalCol_L = 0;
						int gTotalCol_M = 0;
						int gTotalWork = 0;
						
						while(resultSet.next()){
							TreeMap<String, String> c = new TreeMap<String, String>();
							
							int total_work = resultSet.getInt("col_c") + resultSet.getInt("col_d") + resultSet.getInt("col_e") 
									+ resultSet.getInt("col_f") + resultSet.getInt("col_g") + resultSet.getInt("col_h") 
									+ resultSet.getInt("col_i") + resultSet.getInt("col_j") + resultSet.getInt("col_k") 
									+ resultSet.getInt("col_l") + resultSet.getInt("col_m");
							
							gTotalSummission += resultSet.getInt("total_submission");
							gTotalCol_C += resultSet.getInt("col_c");
							gTotalCol_D += resultSet.getInt("col_d");
							gTotalCol_E += resultSet.getInt("col_e");
							gTotalCol_F += resultSet.getInt("col_f");
							gTotalCol_G += resultSet.getInt("col_g");
							gTotalCol_H += resultSet.getInt("col_h");
							gTotalCol_I += resultSet.getInt("col_i");
							gTotalCol_J += resultSet.getInt("col_j");
							gTotalCol_K += resultSet.getInt("col_k");
							gTotalCol_L += resultSet.getInt("col_l");
							gTotalCol_M += resultSet.getInt("col_m");
							gTotalWork += total_work;
							
							c.put("group", resultSet.getString("role_name") == null ? resultSet.getString("c_group_code") : resultSet.getString("role_name"));
							c.put("total_submission", resultSet.getString("total_submission"));
							c.put("col_c", resultSet.getString("col_c"));
							c.put("col_d", resultSet.getString("col_d"));
							c.put("col_e", resultSet.getString("col_e"));
							c.put("col_f", resultSet.getString("col_f"));
							c.put("col_g", resultSet.getString("col_g"));
							c.put("col_h", resultSet.getString("col_h"));
							c.put("col_i", resultSet.getString("col_i"));
							c.put("col_j", resultSet.getString("col_j"));
							c.put("col_k", resultSet.getString("col_k"));
							c.put("col_l", resultSet.getString("col_l"));
							c.put("col_m", resultSet.getString("col_m"));
							c.put("type", resultSet.getString("rpt_type"));
							c.put("matrix", resultSet.getString("rpt_matrix"));
							c.put("total", String.valueOf(total_work));
							
							if(StringUtils.isNotBlank(c.get("type"))){
								String type = c.get("type");
								
								if("New Application".equals(type)){
									if(StringUtils.isNotBlank(c.get("matrix"))){
										if("Non Matrix".equals(c.get("matrix"))){
											cArrNewAppNonMatrix.add(c);
										}else if("Matrix".equals(c.get("matrix"))){
											cArrNewAppMatrix.add(c);
										}else{
											cArrNewAppOther.add(c);
										}
									}else{
										cArrNewAppOther.add(c);
									}
								}else if("Extend Facility".equals(type)){
									cArrExtend.add(c);
								}else if("e-Commerce/M2u".equals(type)){
									cArrECommerce.add(c);
								}else{
									cArrAddtion.add(c);
								}
							}
						}
						TreeMap<String, String> g = new TreeMap<String, String>();
						g.put("gTotalSummission", Integer.toString(gTotalSummission));
						g.put("gTotalCol_C", Integer.toString(gTotalCol_C));
						g.put("gTotalCol_D", Integer.toString(gTotalCol_D));
						g.put("gTotalCol_E", Integer.toString(gTotalCol_E));
						g.put("gTotalCol_F", Integer.toString(gTotalCol_F));
						g.put("gTotalCol_G", Integer.toString(gTotalCol_G));
						g.put("gTotalCol_H", Integer.toString(gTotalCol_H));
						g.put("gTotalCol_I", Integer.toString(gTotalCol_I));
						g.put("gTotalCol_J", Integer.toString(gTotalCol_J));
						g.put("gTotalCol_K", Integer.toString(gTotalCol_K));
						g.put("gTotalCol_L", Integer.toString(gTotalCol_L));
						g.put("gTotalCol_M", Integer.toString(gTotalCol_M));
						g.put("gTotalWork", Integer.toString(gTotalWork));
						
						cArrGrandTotal.add(g);
					} finally {
						resultSet.close();
					}
				
				} finally {
					statement.close();
				}
			} finally {
				conn.close();
			}
			
			result.put("New Application Non Matrix", cArrNewAppNonMatrix);
			result.put("New Application Matrix", cArrNewAppMatrix);
			result.put("New Application Other", cArrNewAppOther);
			result.put("Extend Facility", cArrExtend);
			result.put("e-Commerce/M2u", cArrECommerce);
			result.put("Additional Terminal/mPOS Reader", cArrAddtion);
			result.put("Grand Total", cArrGrandTotal);
			
			return result;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	
	
	public static String getGroupName(String groupCode) {
		String result = "";
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				
				String sql = "select tblaccessrole.role_name "
							+ "from tblaccessrole "
							+ "where 1 = 1 "
							+ "and role_code = ?";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setString(1, groupCode);
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
						
							result= (resultSet.getString("role_name") == null ? groupCode : resultSet.getString("role_name"));
							break;
						}
						
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
				
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return result;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static String getUserName(String groupUser){
		String result = "";
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				
				String sql = "select tbluser.name "
							+ "from tbluser "
							+ "where 1 = 1 "
							+ "and login_id = ?";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setString(1, groupUser);
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
						
							result= (resultSet.getString("name") == null ? groupUser : resultSet.getString("name"));
							break;
						}
						
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
				
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return result;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	

	public static ArrayList<TreeMap<String, String>> getSummOfSummaryReport(TreeMap<String, String> search) {
		ArrayList<TreeMap<String, String>> cArr = new ArrayList<TreeMap<String, String>>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
		
				if(search != null) {
					
					if(search.containsKey("group_code") && search.get("group_code").length() > 0){
						q_search += "and c_group_code = ? ";
					}
					
					if(search.containsKey("application_type") && search.get("application_type").length() > 0){
						q_search += "and c_type = ? ";
					} else {
						q_search += " and ((c_type like 'New Application%') " +
								" or (c_type like 'Extend Facility%') " +
								" or (c_type like 'Additional Terminal%')" +
								" or (c_type like 'e-Commerce/M2u%')) ";
					}
					
					if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
						q_search += "and c_reseller_location = ? ";
					}
					
					if(search.containsKey("process") && search.get("process").length() > 0){
						q_search += "and rpt_process = ? ";
					}
					
					if(search.containsKey("matrix") && search.get("matrix").length() > 0){
						q_search += "and rpt_matrix = ? ";
					}
					
					if(search.containsKey("from_date") && search.get("from_date").length() > 0 && search.containsKey("to_date") && search.get("to_date").length() > 0){
						q_search += "and rpt_date >= to_date(?, 'DD/MM/YYYY') "
									+ "and rpt_date <= to_date(?, 'DD/MM/YYYY') ";
					}
					
				}
				
				
				String sql = "select "
						+ "count(rptsummary.rpt_case_id) as total_submission, "
						+ "sum ( case when UPPER(rpt_action) = 'APPROVED. TO ISSUE NEW LETTER OF ACCEPTANCE' then 1 else 0 end ) as col_c, "
						+ "sum ( case when UPPER(rpt_action) = 'APPROVED. REQUEST FOR MID SETUP' then 1 else 0 end  ) as col_d, "
						+ "sum ( case when UPPER(rpt_action) = 'RECOMMENDED' then 1 else 0 end  ) as col_e, "
						+ "sum ( case when UPPER(rpt_action) = 'DECLINED' then 1 else 0 end  ) as col_f, "
						+ "sum ( case when UPPER(rpt_action) = 'CLOSE - CANCEL' then 1 else 0 end  ) as col_g, "
						+ "sum ( case when UPPER(rpt_action) = 'CLOSE - WITHDRAW' then 1 else 0 end  ) as col_h, "
						+ "sum ( case when UPPER(rpt_action) = 'CLOSE - REJECT' then 1 else 0 end  ) as col_i, "
						+ "sum ( case when UPPER(rpt_action) = 'REJECT - DATA DISCREPANCY' then 1 else 0 end ) as col_j, "
						+ "sum ( case when UPPER(rpt_action) = 'REJECT - DOC DISCREPANCY' then 1 else 0 end ) as col_k, "
						+ "sum ( case when UPPER(rpt_action) = 'REJECT - DATA & DOC DISCREPANCY' then 1 else 0 end ) as col_l, "
						+ "sum ( case when UPPER(rpt_action) = 'COMPLETED' then 1 else 0 end ) as col_m "
						+ "from rptsummary "
						+ "left join casemast on rptsummary.rpt_case_id = casemast.c_id "
						+ "left join tblaccessrole on casemast.c_group_code = tblaccessrole.role_code "
						+ "where 1 = 1 "
						+ q_search; 
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					
					int last_index = 0;
					
					if(search != null) {
						
						if(search.containsKey("group_code") && search.get("group_code").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("group_code"));
						}
						
						if(search.containsKey("application_type") && search.get("application_type").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("application_type"));
						}
						
						if(search.containsKey("reseller_location") && search.get("reseller_location").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("reseller_location"));
						}
						
						if(search.containsKey("process") && search.get("process").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("process"));
						}
						
						if(search.containsKey("matrix") && search.get("matrix").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("matrix"));
						}
						
						if(search.containsKey("from_date") && search.get("from_date").length() > 0 && search.containsKey("to_date") && search.get("to_date").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("from_date"));
							++last_index;
							statement.setString(last_index, search.get("to_date"));
						}
						
					}
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							TreeMap<String, String> c = new TreeMap<String, String>();
							int total_work = resultSet.getInt("col_c") + resultSet.getInt("col_d") + resultSet.getInt("col_e") 
									+ resultSet.getInt("col_f") + resultSet.getInt("col_g") + resultSet.getInt("col_h") 
									+ resultSet.getInt("col_i") + resultSet.getInt("col_j") + resultSet.getInt("col_k") 
									+ resultSet.getInt("col_l") + resultSet.getInt("col_m");
							
							c.put("total_submission", resultSet.getString("total_submission"));
							c.put("col_c", resultSet.getString("col_c"));
							c.put("col_d", resultSet.getString("col_d"));
							c.put("col_e", resultSet.getString("col_e"));
							c.put("col_f", resultSet.getString("col_f"));
							c.put("col_g", resultSet.getString("col_g"));
							c.put("col_h", resultSet.getString("col_h"));
							c.put("col_i", resultSet.getString("col_i"));
							c.put("col_j", resultSet.getString("col_j"));
							c.put("col_k", resultSet.getString("col_k"));
							c.put("col_l", resultSet.getString("col_l"));
							c.put("col_m", resultSet.getString("col_m"));
							c.put("total", String.valueOf(total_work));
							
							cArr.add(c);
						}
						
					} finally {
						resultSet.close();
					}
				
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return cArr;
			
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	public static ArrayList<String> getStatusfromProcess2(TreeMap<String, String> search) {
		ArrayList<String> rtn = new ArrayList<String>();
		
		try{
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String q_search = "";
				
				if (search != null){
					if(search.containsKey("process") && search.get("process").length() > 0){
						q_search += "and s_process = ? ";
					}
				}
				
				/*String sql = "select distinct s_next "
							+ "from statusmast "
							+ "where 1 = 1 "
							+ q_search
							+ "order by s_process asc"; */
				
				String sql ="select distinct s_next, 1 as x  from statusmast where  s_next = 'PUT UP' " +
						"union " +
						"select distinct s_next, 2 as x  from statusmast where s_next <> 'PUT UP' " +
						"order by x";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					int last_index = 0;
					System.out.println(search);
					/*if (search != null){
						if(search.containsKey("process") && search.get("process").length() > 0){
							++last_index;
							statement.setString(last_index, search.get("process"));
						}
					}*/
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							rtn.add(resultSet.getString("s_next"));
						}
					} finally {
						resultSet.close();
					}
				
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return rtn;
				
		} catch (Exception e){
			Report.message = e.getMessage();
			e.printStackTrace();
			return null;
		}
	}
	
	private static int getDurationDate(String sDate, String eDate){
		int numOfDays = 0;
		
		if(StringUtils.isNotBlank(sDate) && StringUtils.isNotBlank(eDate)){
			System.out.println("start: " + sDate + ", end: " + eDate);
			SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
			
			try {
				Date startDate = formatter.parse(sDate);
				Date endDate = formatter.parse(eDate);
			
				if (startDate.after(endDate)) {
					throw new IllegalArgumentException("End date should be grater or equals to start date");
				}

				Calendar calendar = Calendar.getInstance();
				calendar.setTime(startDate);

				while(!calendar.getTime().after(endDate)) {
					int dayOfWeek = calendar.get(Calendar.DAY_OF_WEEK);
					if ( (dayOfWeek>1) && (dayOfWeek<7) ) {
						System.out.println("check: " + DateFormatUtils.format(calendar.getTime(), "dd/MM/yyyy"));
						if(!isHoliday(DateFormatUtils.format(calendar.getTime(), "dd/MM/yyyy"))){
							numOfDays++;
						}
					}
					calendar.add(Calendar.DAY_OF_MONTH, 1);
		    	}
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	    return numOfDays;
	}
	
	private static boolean isHoliday(String strDate){
		if(StringUtils.isNotBlank(strDate)){
			ArrayList<String> rtn = new ArrayList<String>();
			
			try{
		        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
		        String connectionPool = ConfigManager.getInstance().getConnectionPool();
		        DBConnectionManager connMgr = DBConnectionManager.getInstance();
		        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
		        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
				
				try {
					String sql ="select count(*) as holiday from publicholiday where ph_date = to_date(?, 'DD/MM/YYYY')";
					
					PreparedStatement statement = conn.prepareStatement(sql);
					
					try {
						statement.setString(1, strDate);
						
						ResultSet resultSet = statement.executeQuery();
						
						try {
							while(resultSet.next()){
								if(StringUtils.isNotBlank(resultSet.getString("holiday"))){
									return true;
								}
							}
						} finally {
							resultSet.close();
						}
					
					} finally {
						statement.close();
					}
				} finally {
					connMgr.freeConnection(connectionPool, conn);
				}
				
				return false;
					
			} catch (Exception e){
				Report.message = e.getMessage();
				e.printStackTrace();
				return false;
			}
		}else{
			return false;
		}
	}
}